CTEs are cool things. You can essentially create one or more in-line view(s) within your query. One thing that isn’t overly well known is that you can actually update the data within the CTE. No, I don’t mean using using the UPDATE statement with a CTE but actually running the update through the CTE.

WITH myCTE AS (SELECT * FROM Purchasing.ShipMethod
WHERE ShipBase > 20.00)
UPDATE myCTE SET ShipRate = ShipRate + 1;

Now, I want to say up front (or at least a paragraph or so in) that I’m almost certain I’ve written about this before, but can’t for the life of me find where I did. So if I did, and you’ve read it, great! You get a reminder! If I haven’t then, great! This is a cool feature you should know about!

So what good is this? Well, I’m not going to go into why you would want to use a CTE here, because of course my UPDATE above could easily be re-written as:

UPDATE Purchasing.ShipMethod
SET ShipRate = ShipRate + 1
WHERE ShipBase > 20.00;

What I’m going to discuss is the difference between these two statements:

WITH myCTE AS (SELECT ShipMethodID FROM Purchasing.ShipMethod
WHERE ShipBase > 20.00)
UPDATE Purchasing.ShipMethod
SET ShipRate = ShipRate + 1
FROM Purchasing.ShipMethod
JOIN myCTE 
ON Purchasing.ShipMethod.ShipMethodID = myCTE.ShipMethodID;
-- VS
WITH myCTE AS (SELECT * FROM Purchasing.ShipMethod
WHERE ShipBase > 20.00)
UPDATE myCTE SET ShipRate = ShipRate + 1;

Well, firstly it’s obviously a lot less complicated. Secondly, the updateable CTE version tends to be faster. It certainly uses less IO. Here are the results with SET STATISTICS IO ON.

-- Join CTE to original table (long version)
Table 'ShipMethod'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(2 row(s) affected)
-- Updateable CTE (short version)
Table 'ShipMethod'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(2 row(s) affected)

This is an insanely small query (heck the table is only 5 rows long), but even so, you can see a big IO difference. You aren’t likely to see a useful speed difference here but that big an IO difference is highly likely to show a performance difference as well.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: ctes, microsoft sql server, T-SQL