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.*)

 

 



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Finding changed rows in SQL Server using INTERSECT, ISNULL or COALESCE and performance | 0 comments | Create New Account
The following comments are owned by whomever posted them. This site is not responsible for what they say.


Gold Coast Aquarium Maintenance | Gold Coast Marine Fish | Gold Coast Tropical Fish
Jewel Jones - Counselling Service - Penrith, Richmond
 
     
 Copyright © 2017 Julian Kuiters
 All trademarks and copyrights on this page are owned by their respective owners.