June 12, 2007

SQL Server 2005's EXCEPT Statement

by Brian Donahue

After discovering (and understanding) SQL Server 2005's ROW_NUMBER() function, I thought that alone was enough to make me appreciate the new version of SQL Server.  Well, recently I discovered the EXCEPT statement and I am really glad that T-SQL has added it to the toolbox.  The EXCEPT statement recently provided a great performance boost on a large nightly data import I created for a client.  Basically, the EXCEPT statement compares two similar queries (same columns) and returns the rows from the first result set that do not exist in the second result set.  In my particular example, this allowed me to not have to blindly overwrite all records that were imported, but only do an update for records where the data actually changed.  For example:

SELECT productTypeId, name, price FROM NewProducts
EXCEPT
SELECT productTypeId, name, price FROM Products

The above query would return any records in NewProducts that did not have a matching record in Products.  You could then add the new rows to your Products table.  In my case, I used EXCEPT to get all new or changed records in the imported data set and then do inserts or updates on my existing data.  It reduced my number of nightly updates/inserts from ~10000 to about 1500.