Common Table Expressions in SQL Server 2005

  • Hi,

    I don't know the term "forward-referenced", sorry.


    Today a new article of Srinivas is published under the link,

    This article summarizes the recursive usage of common table expressions very well with a few good examples.

    I think if we compare CTE and temp tables, temp tables are more flexable. But if do not need the table repeatedly in your codes, you can choose CTE.





  • Hm, well I took it to mean the data in the table can be modified after it's creation. From the article:

    Note that by defining multiple CTEs, you can incrementally build on the earlier CTEs or define new results that are then used later on. Note however, that you cannot create a CTE that uses forward-reference (a CTE that is yet to be defined).

    Everett Wilson

  • If I repeat the same derived table 2 or 3 times in my SQL select statement, the execution plan looks like it executes the same select code several times.  Do CTEs make the execution plan more efficient in such cases or is it merely a (very) nice shorthand?

  • Srinivas Sampath:

    Very nice job with almost no extra words in explain the CTE.

    Some thoughts:

    1. It looks like CTE could replace table variable in most of the case, is this right?

    2. How long the live of CTE will be lasted? If is created in procA and it at the end called procB, could the same CTE be used in procB as a ## global temp table?

    Want to see more articles from you!




    Hi David,

    As far as I know you can only use a CTE just after you define it.




  • Very good informative article.

    Put me right on my way to 2005.

  • Ramesh,

    Yes you can use variables in CTEs.


  • Hi All,

    I would like to know which is better in terms of performance: CTE or Temporary Table?



  • Fantastic article!  Clear and concise.

  • Another option is to use a table variable, populate it with data, then you can reference it multiple times.  Perhaps not as fast as a CTE (I don't have SS 2005 to test), but available in SS 2000.


  • Hi all,

    I noticed a lot of discussions on the cte feature of 2k5.

    My experiemnation on this feature tend to show the following:

    1. CTE is a powerful standalone tool to query a mix of heirarchical and flat data across multiple database tables. It can be enhanced to do more and more complex queries with ease.

    2. The performance has been mostly less [about 70% of conceivable situations] than convetional queries. Only in a few situations that too in tree like data it beat conventional query

    3. Main drawback for the cte is when it is used in a generic function/view. We loose most important feature of indexing for views when cte is a part of the view.

    4. For obvious reasons left/right outer joins are not acceptable in a cte. These features are required in many business logics though.

    5. Some of the experimentations I did make me beikeve that when cte view is called from out side with a parametric filter, query plan still shows all the rows in the view are first returned and then filtered in next step.

    I may come with specific examples when free, but these are my first readinds.

    I guess it is a new and powerful tool, but needs more features to accommodate most business logics.


  • I like the way the article was written, but the examples were trivial ones that could easily be done with a simple select. The recursive article linked to in the comments had great examples. I tried writing/modifying this as a test, to see if I understand how the recursion works:

    WITH Ancestor (PersonID, ChildID, Name, SortKey) AS


    -- Create the anchor query. This establishes the starting

    -- point


    p.PersonID, p.ChildID, p.Name, CAST(p.SortKey + CAST (p.ChildID AS BINARY(4)) AS VARBINARY(900))

    FROM dbo.Person p

    Where p.PersonID = @root


    -- Create the recursive query. This query will be executed

    -- until it returns no more rows


    p.PersonID, p.ChildID, p.Name, CAST(p.SortKey + CAST (p.ChildID AS BINARY(4)) AS VARBINARY(900))

    FROM Ancestor a

    INNER JOIN Person p on a.ChildID = p.PersonID


    SELECT * FROM Ancestor ORDER BY SortKey

    Note the reference to the CTE from within itself, with the first part of the union making up a seed table and the next part of the union feeding on the seed, then itself until no more rows are returned. That's pretty clever. And it's ANSI standard, (While "while" isn't, right?) so who can complain?


    Signature is NULL

  • Yes!  I've been working on a very complex GROUP BY query breaking down timestamps into variable hourly groupings. (1 hour, 2 hours, 3 hours etc.)  I've had to copy this complex once for each possible hour group request to include everything I need in the output.  Your instruction has given me a new outlook on how to prep the data once, then populate from the results as needed!



  • Great article.  Thank you for information.

    Question:  How does this compare performance wise with using Table-Value Functions for joins?

  • Great explanation of CTE's, thanks!

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply