Efficient way to compare millions of data between 2 tables that have exact count of rows and same data types in SQL Server

I have 2 different tables that have same field names and data types. Even the count of records from each table are the same. They are in the same database. Each table has millions of data in them. Is there an efficient way to compare data between these 2 tables?

When I tested with a small subset of data, I noticed that there are differences in the values within the fields. Ex: field1 from table 1 stored null value and same field1 from table2 stored blank value. I need to find the differences like these , basically find what is exactly different between these 2 tables so that we can fix it.

I tried to use except and union all commands but it takes forever to execute the query

I posted a comment but i figured an answer is good to.

EXCEPT is the operator you can use to find differences.

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql?view=sql-server-ver16

 SELECT * FROM YourTable1
 EXCEPT
 SELECT * FROM YourTable2

This query will return results from the 2nd query not matching those in the first query.

I believe @Doug-Coats answer will probably be apt for your use-case as I understand it. But your scenario could be more complex.

Consider that approach can fail where identity columns are used and the same data exists in each table but in a row with a different ID

Notice how the only word in one, but not both tables, is "an", but the use of EXCEPT brought other rows back because the ID column (which I defined as PK and an identity) differ. You have the same fundamental data, just at a different position in the table relative to the other, which the ID and/or identity column when considered by the EXCEPT operator will treat as different.

So if your use-case has identity/ID columns with data in different order between the tables, then it may not give the results you are looking for.

There are 2 ways around this. The first is to SELECT your table data excluding your PK and/or identity column when using EXCEPT - but this might lose you the ability to trace back to your source data.

For the other...

You should consider what makes your data unique from your point of view. Use whatever columns are not a PK/identity and could be used to generate a checksum. These are the collection of columns you would manually inspect to determine if rows contain different content (if you really had to), regardless of PK/identity value on the row.

You can compute a checksum value using BINARY_CHECKSUM(<column-list>) AS [chksum]

You can SELECT the data with checksum in to a suitable table. Then you are looking for where there are checksums in Table_1 not existing in Table_2 and vice-versa, together with a means of identifying the table the data is sourced from when the result is output.

I personally prefer stored procedures for such things, which would declare two table variables which match your table schemas, except there is an additional 'chksum' column suitable for your checksum. I would then create a third table variable with a 'source' column for containing the results.

I would then do something like this:

INSERT @Table1WithChksum
SELECT *, BINARY_CHECKSUM(<column-list>) AS [chksum] FROM dbo.Table_1; -- replace * and <column-list> with your actual column list

INSERT @Table2WithChksum
SELECT *, BINARY_CHECKSUM(<column-list>) AS [chksum] FROM dbo.Table_2; -- replace * and <column-list> with your actual column list

INSERT @FilteredResults
SELECT *, 'In Table_1, not Table_2' AS [source] 
   FROM @Table1WithChksum t1
   LEFT JOIN @Table2WithChksum t2
   ON t1.[chksum] = t2.[chksum]
   WHERE t2.[chksum] IS NULL;

INSERT @FilteredResults
SELECT *, 'In Table_2, not Table_1' AS [source] 
   FROM @Table2WithChksum t2
   LEFT JOIN @Table1WithChksum t1
   ON t2.[chksum] = t1.[chksum]
   WHERE t1.[chksum] IS NULL;

-- then do a final select
SELECT * FROM @FilteredResults -- replace * with your column list

You then know which 'unique' data is in either table but not both, regardless of differing PK or identity, or order within the table. Items with 'source' as "Table_1" have values unique to "Table_1" and not in "Table_2" and vice-versa.

If checksums in a table are unlikely to be duplicated, it is probably best to define your table variable [chksum] column with a non-unique index to speed up the JOIN queries.