SQL Server 2005 Common Table Expressions

On June 24, 2008, in SQL, by Anuj Gakhar

I was recently stuck with a problem where I had to loop over a self referencing table (unlimited parent child relationships from the same table) and generate a tree structure upto n levels and because my table didnt have the levels defined in it, the tree leveles were to be defined at run time. What most people would do in this case is start from parent level and loop over until you reach the last level, this loop can be either in your scripting language (CF in my case) or a recursive stored procedure or a really complex query. I would have done the same if I didnt come across Common Table Expressions (CTE’s) which were introduced in Sql Server 2005.

A CTE is a better form of a derived table that needs to be declared only once and can be referenced or recursively defined multiple times in a query.  Consider a scenario with the following data :-

Now with the above example, if you wanted to generate a hierarchical dataset with everything in it, and an additional column called [Level] that would tell you at what level the category is, its pretty easy to do so with a CTE. Here is how it looks like :-

[sql]
WITH  CatTree  (CategoryID, ParentID, CategoryName, Level)
AS
(
SELECT CategoryID, ParentID, CategoryName, 0 As [Level]
FROM tblCategory WHERE ParentID = 0

UNION ALL

SELECT C.CategoryID, C.ParentID, C.CategoryName, Level + 1
FROM tblCategory C
INNER JOIN     CatTree ON
CatTree.CategoryID= C.ParentID

)

SELECT * FROM CatTree  Order By Level
[/sql]

So as you can see, its pretty easy to understand this code.  A CTE can be considered as another form of View but with more capabilities. In the above query, I start off the query by giving a name to the CTE (CatTree), and then assigning the columns I want it to have. Notice, the column [Level] is not in the actual table, its a derived column which is computed as part of the recursive CTE call.

The trick is that after the CTE query, you immediately need to fire a SELECT query which uses the CTE, otherwise the CTE is no longer available. This is a MUST. The select query should be immediately after the CTE.

Running the above query, gives a result like this.

So as you can see, the column [Level] has been computed and can now be used to generate n level of nested structures starting from any particular level or category in the tree.  It is quite handy when you can work with derived/computed columns all in one query.  If you are using ColdFusion, you could save this query in memory and then do a query of queries whereever you wanted to retrieve only certain level of categories etc… in your application.

Here is the some documentation on CTE’s for those who want to read on.

Tagged with:  

11 Responses to SQL Server 2005 Common Table Expressions

  1. Vaishali says:

    don’t interested to go for double scanning the same. As it is scanning my table twice, whereas i am using it once only in cte..
    Would you please be able to provide the solution without 2 scan..or in 1 query

  2. Anuj Gakhar says:

    It has to be a recursive loop to be able to compute the derived column. are you trying to do something else?

  3. Evan says:

    CTE’s are one of the best SQL 2005 features, IMO.

    CTE make short work of the classic “I need to collapse a series of rows into a delimited string” problem. I’ve seen several articles suggesting that the “FOR XML PATH(”)” construct is the best way to solve such a problem, but I’ve had great success using CTEs to do it.

  4. Matt Potter says:

    I’m trying to find a way where I can order the parent and children in such a way you would get the level 2 children directly under it’s correct level 1 parent (i.e “do it the hard way” under “.NET”) rather than listing all level 1 children and then all level 2 children.

    Any ideas would be much appreciated.

  5. Anuj Gakhar says:

    @Matt, I guess that requires some code to be written. You would have to run some recursive loops to generate this ‘tree’ structure.

  6. Matt Potter says:

    Have you got any examples of this? I’m not exactly sure by what you mean by “run some recursive loops”.

  7. Anuj Gakhar says:

    @Matt, What I mean is, you would take the resultset and group by level and then run a query of query to order the items as you want them. ie if you are doing this in ColdFusion. There might be other ways of doing this as well…

  8. Matt Potter says:

    Thanks for your comments. Your help is much appreciated.

  9. James says:

    I would like to know if there is a way to get all parent objects of a given child, so in the above example i could pass in category 11 and i would get all the results that roll up from there. not sure how i can accomplish this

    Thanks

  10. Anuj Gakhar says:

    @James in ColdFusion, you could do that with a query of queries , not sure about other languages though.

  11. Anthony Topper says:

    @Anuj Depending on what you are doing Query of queries could less efficient than a CTE.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2011 Anuj Gakhar
%d bloggers like this: