October 18, 2017 by Kenneth Fisher
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.
Category: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL | Tags: ctes, microsoft sql server, T-SQL
5 thoughts on “Updateable CTEs”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
[…] Kenneth Fisher shows that you can directly update a table referenced in a common table expression: […]
I’ve used CTE’s in MERGE code: most of the time it is the “source” table, but I have used a CTE for the target table. The reason this is cool is that I can DELETE in the MERGE statement, but limit its impact due to the definition I’ve established in the CTE.
Absolutely. Updating the CTE can have a lot of benefits.
[…] Read More (Community content) […]
[…] And this works fine. But I remembered that you can update (and probably delete) from a CTE. […]