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;
;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