Welcome to Julian Kuiters Friday, October 19 2018 @ 08:57 AM AEDT

Finding changed rows in SQL Server using INTERSECT, ISNULL or COALESCE and performance

SQL Server 2008

If you are trying to determine which rows are different between two sets, the usual method is to create a query comparing table1.Column1 != table2.Column1 but this doesn't check for rows where one of these columns is null.

isnull(table1.Column1,-1) != isnull(table2.Column1,-1)    might seem like a good way to compare nullable columns - but the performance of this is still not optimal.

Paul White has written a great article on using the INTERSECT command (a feature in SQL Server 2005 and SQL Server 2008) to compare two sets looking for differences and why this performs much better inside SQL Server.

Paul's article details the different query plans that are created by using ISNULL and COALESCE and INTERSECT to do the comparisons, including the internal predicates that SQL Server uses to fulfill the query.

In short an INTERSECT handles null comparisons and is certainly much simpler to write. Below is a quick sample of the INTERSECT example Paul gives (for my own reference), but I highly recommend reading the article for the full details.

SELECT *
FROM  table1 as T1
JOIN table2 as T2 ON  T1.pk = T2.pk
WHERE NOT EXISTS
          (SELECT T1.* INTERSECT T2.*)