Demystifying CTEs

One of the super cool things I love about SQL Server 2005 is the introduction of CTEs. What is a CTE? CTE stands for Common Table Expression. If you’re still scratching your head, I know how you feel. The name doesn’t really convey what it is and what’s super cool about it. BOL refers to a CTE as a “temporary named result set”. Sound somewhat familar? Let’s keep going. It’s similar to a derived table, subquery, or a temp table. What’s cool about it is you can use it to replace subqueries, temp and derived tables. What’s even cooler is you can use it for selects, inserts, updates, and deletes. I personally like them because I think it makes code maintenance and troubleshooting way easier. You can use CTEs for recursive queries such as hierarchies and to replace views. They can be used in functions, stored procedures, views, triggers, and *gasp* cursors. Pretty cool, huh?
It sounds almost too good to be true, doesn’t it? As with everything it seems, there’s always a catch. So what’s the catch? The scope is limited. Limited how, you ask? Once you define your CTE, you have to use it right away. Meaning, you can’t define your CTE, run some other queries, and then use your CTE. You have to use it in a select, update, insert, or delete right after you define it. The other downside? You can’t put an order by in your definition. Let’s take a closer look at what it looks like and then hopefully it’ll make more sense.

My Kingdom for a CTE

 

;WITH MyKingdom
(royalID, royalName, royalTitle)
AS
( SELECT Kingdom.royalID
 ,Kingdom.firstName + ' ' + lastName
 ,Title.titleName
  FROM dbo.Kingdom
  INNER JOIN dbo.Title
  ON Kingdom.titleID = Title.titleID
)

SELECT   royalID
           ,royalName
           ,royalTitle
FROM MyKingdom
ORDER BY royalID;
This is a basic non-recursive CTE. The definition includes “WITH” which must be preceded with a semicolon if you have code before it. I always include it to be on the safe side. After the “WITH” is the name of your CTE followed by a list of columns you want it to use followed by “AS” and parenthesis around your query. Next you use your CTE in a query. This query can be a select, insert, update, or delete. It’s pretty neat. Here’s what the result set from the above CTE.
  
  
Oh Yeah! Can’t Forget About Multiples!
Another cool thing you can do with CTEs is to define one right after the other and join them in one query like so.
;WITH MyKingdom
    (royalID, royalName, royalTitle)
 AS
 ( SELECT Kingdom.royalID
   ,Kingdom.firstName + ' ' + lastName
     ,Title.titleName
   FROM dbo.Kingdom
   INNER JOIN dbo.Title
   ON Kingdom.titleID = Title.titleID
 )
 , WhoOwesMe
     (royalID, taxYear, taxAmount)
 AS
 ( SELECT royalID
  ,taxYear
  ,taxAmount
   FROM dbo.Treasury
   WHERE taxAmount > 0
)
SELECT   MyKingdom.royalID
 ,royalName
 ,royalTitle
 ,isnull(WhoOwesMe.taxAmount,0) taxOwed
 FROM MyKingdom
 LEFT JOIN WhoOwesMe
 ON (MyKingdom.royalID = WhoOwesMe.royalID)
 ORDER BY royalID;
The result looks something like this:

 
Recursive CTEs - Who Reports to Whom?
Now for the more complicated situations. Have you ever had to code something hierarchical such as who reports to whom in your organization? It's not all that easy to do, in my opinion. Lucky for us, CTEs make it way easy!
;WITH MyKingdomHierarchy (royalID, royalName, royalTitleID, rulerID,hierarchyLevel)  
AS  
( --Anchor member   
SELECT royalID
    ,firstName + ' ' + lastName
    ,titleID
    ,rulerID
    ,1 as hierarchyLevel
FROM dbo.Kingdom   
WHERE rulerID = 0   
UNION ALL   
--Recursive member   
SELECT royalID
   ,firstName + ' ' + lastName
   ,titleID
   ,rulerID
   ,MyKingdomHierarchy.hierarchyLevel + 1 as hierarchyLevel   
FROM dbo.Kingdom   
WHERE rulerID > 0  
)
SELECT MyKingdomHierarchy.royalID
  ,MyKingdomHierarchy.royalName
  ,Title.titleName royalTitle
  ,MyKingdomHierarchy.rulerID
  ,MyKingdomHierarchy.hierarchyLevel
FROM MyKingdomHierarchy  
INNER JOIN dbo.Title
ON MyKingdomHierarchy.royalTitleID = Title.titleID
ORDER BY MyKingdomHierarchy.royalID; 
The important parts are the “anchor” query followed by the recursive member query. The anchor query defines the top level. The recursive member is simply everyone below the top level. You need to make sure to include the hierarchy level or it won’t quite work. When the above CTE is run, the results look like this:
And there you go! That’s pretty much all there is to it. Hopefully it makes sense! If not, let me know and I’ll do my best to clarify it.
 
Update: If you’re interested in using the example tables I created, you can download the script here: demystifying_ctes_script
Advertisements

17 thoughts on “Demystifying CTEs

  1. So how is a CTE with a multiple select different from one with a Union?

    How is it used with Inserts, Updates, Deletes? Can you update the CTE directly? For instance can your “multiples” example can you replace the final select to:
    UPDATE MyKingdom
    SET amountOwed = 500
    WHEREroyalTitle = ‘surf’

    1. Excellent questions! The multiple CTEs can accomplish the same thing as a union. It’s just a different way of doing it. I mainly use them to join together different tables that have complicated logic.

      As for the insert, update, and delete… I knew I forgot something! 🙂 Yes, you would replace the select statement that invokes the CTE with the insert, update, or delete. You should be able to replace the last select with that update statement.

      Hope that helps. I’ll work on adding that to my blog. Thanks 🙂

      1. Interesting and powerful. I see I should also review my typing when it is so late 🙂

        I don’t write much t-sql anymore, but did use a CTE a couple years ago to assist our development team to rewrite 20 printed pages of some nasty cursor logic and bring the performance of a report from 3 1/2 hours to 1 1/2 minutes!

        I will need to present them to our development team again.

  2. Pingback: Demystifying CTEs
  3. Pingback: Demystifying CTEs
  4. Hello there. I wonder if there is any chance for a backup copy of the database you’re querying here, or the SQL to create the tables referred to in this example?

    Many thanks 🙂

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