Updateable CTEs

3

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.

3 thoughts on “Updateable CTEs

  1. […] Kenneth Fisher shows that you can directly update a table referenced in a common table expression: […]

  2. DEK46656 says:

    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,146 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: