weblogs.sqlteam.com
The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !
The basic idea is: if we GROUP the union of two tables on all columns, then if the two tables are identical all groups will result in a COUNT(*) of 2. But for any rows that are not completely matched on any column in the GROUP BY clause, the COUNT(*) will be 1 – and those are the ones we want. We also need to add a column to each part of the UNION to indicate which table each row comes from, otherwise there is no way to distinguish between which row comes from which table.