Technique 1: Min w/ UnionLee Everest proposed this method in his blog, Find Differences Between Two Tables in TSQL.
SELECT MIN(id) AS id, col1, col2, col3 FROM (SELECT id, col1, col2, col3 FROM tbl1 UNION ALL SELECT id, col1, col2, col3 FROM tbl2) X GROUP BY col1, col2, col3 HAVING COUNT(1)=1Due to differences on my db instance, I only had one row different out of 50,000,000 instead of the 3 he had. It ran in 30 seconds.
Technique 2: ExceptI then came across this version on MSSQLTips.com, Compare SQL Server Datasets with INTERSECT and EXCEPT which also correctly found the differences but took 1:37.
Technique 3: Use the SQL Server Data Comparison Tool
Built into Visual Studio is a menu called Data. This has a comparison tool that compares selected data columns from two tables or views. I found it slow and buggy but it spared me having to write a ton of comparison to get an overview of the differences in the data. Sadly once it found the differences, to investigate them, I wrote a lot of SQL still.