How to get Distinct Count across Multiple Tables in SQL Server

Hi Friends,in this article I will explain about  How to get Distinct Count across Multiple Tables in SQL Server.
I already explained in the previous articles about How to Remove/Delete duplicate records/Rows from a table in SQL Server,SQL Server 2008 : How to Remove Special Characters in a String , STUFF and PATINDEX Examples and How to alter primary key column for existing table in sql server 2008
Before knowing How to get Distinct Count across Multiple Tables first know about UNION ALL Operator.

UNION ALL OPERATOR:The SQL UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. It returns all rows from the query (even if the row exists in more than one of the SELECT statements).
Each SELECT statement within the UNION ALL must have the same number of fields in the result sets with similar data types.

To get Distinct Count across Multiple Tables in SQL Server i took two temp tables as shown in the below.

Distinct Count across Multiple Tables in SQL Server:

DECLARE @TABLE1 TABLE (   Fname VARCHAR(50),   lastname VARCHAR(50) ) INSERT INTO @TABLE1   VALUES ('Kishore', 'P') INSERT INTO @TABLE1   VALUES ('SaiR', 'P') INSERT INTO @TABLE1   VALUES ('Krish', 'S') DECLARE @TABLE2 TABLE (   id INT,   Fname VARCHAR(50) ) INSERT INTO @TABLE2   VALUES (1, 'Kishore') INSERT INTO @TABLE2   VALUES (2, 'SaiR') INSERT INTO @TABLE2   VALUES (3, 'Anil') SELECT   nt.Fname,   COUNT(nt.Fname) AS NameCount FROM (SELECT   Fname FROM @TABLE1 UNION ALL SELECT   Fname FROM @TABLE2) nt GROUP BY nt.Fname

In the above query I do UNION ALL two tables to get unique data.

The output of above query as shown in the below figure.