Common Table Expressions in SQL Server 2005

  • Srinivas Sampath

    SSChasing Mays

    Points: 606

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sSampath/commontableexpressionsinsqlserver2005.asp

    HTH,
    Srinivas Sampath
    Blog: http://blogs.sqlxml.org/srinivassampath

  • Jeff Moden

    SSC Guru

    Points: 994239

    Nice job and you got right to the point.  Great comparison example against derived tables.  My hat's off to you.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • hodgy

    SSCertifiable

    Points: 5685

    great article! CTEs were well explained and the examples demonstrated the application of CTEs effectively and succinctly. cheers.

    Life: it twists and turns like a twisty turny thing

  • Parker Smith

    Mr or Mrs. 500

    Points: 591

    I wiould urge DBA's to be mindful of places where CTEs get used, but views would provide either better performance or a resource that could be used in several places.

    Is there any information on the performance of a CTE vs. that of a view?

  • Eralper

    SSCarpal Tunnel

    Points: 4438

    Hi all,

    CTE 's are really powerful with their recursive usages.

    I have also written an article on http://www.kodyaz.com/article.aspx?ArticleID=18 about Common Table Expressions.

    You may check it for the examples...

    Thanks.

    Eralper

    http://www.kodyaz.com

     

     

     

  • Eralper

    SSCarpal Tunnel

    Points: 4438

    Hi Parker,

    As far as I experienced, CTEs are using same execution plans as a normal view query.

    So as you wrote I believe it makes no sense to use CTEs instead of views.

     

    But if you are working with hierarchical data then CTEs are the address to look for a reliable solution

     

    Eralper

    http://www.kodyaz.com

     

  • Ram-200771

    SSC Rookie

    Points: 41

    good material. looking forward for more enhanced feature in sql 2005

  • Tatsu

    SSCertifiable

    Points: 7824

    I would argue that CTEs are an excellent development tool at the very least. They might point out an area where a view might be put in place but why create a view until you need it. I would also consider using a CTE if there was only one query that needed it. If I needed it in another place I would create the view and refactor the original query to use the view instead since I would surely have used stored procedures.

    [font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
    Business Intelligence Administrator
    MSBI Administration Blog

  • Eralper

    SSCarpal Tunnel

    Points: 4438

    Hi Bryant,

    I think that in sql statements where you can use CTEs as views, there exists ways of writing the CTE as an inner join or sub query.

    Also, I tested with a few samples and saw that there is not a notiable increase in performance.

    So I meant that the real reason or necessity for a CTE is not the usage of CTE instead of a view. You are right you should not need to create a view if it will be used once. But you can not also refer to a CTE for a second time. You can use it just after the definition of the CTE.

    But even this usage is a plus and gives developers a flexiblity in their coding processes.

    And I tried to mention that the real power of a CTE is visible when it is used as a recursive common table expression.

     

    Eralper

    http://www.kodyaz.com

  • RTSQL

    Old Hand

    Points: 334

    I can see the use of this CTE in many places. Since I don't have the sql 2005 installed, is it possible to use variables inside the CTEs? The below example is for illustration purpose only and of no practical use i think of:

    declare @maxcount as int

    set @maxcount=10

    WITH TitleCount (authorID, titleCount) AS

    (

    SELECT au_id, COUNT(title_id)

    FROM titleauthor

    GROUP BY au_id having count(title_id) <= @maxcount

    )

    SELECT au_id, au_lname, au_fname, titleCount

    FROM authors a

    INNER JOIN TitleCount

    ON TitleCount.authorID = a.au_id

    Thanks

  • Eralper

    SSCarpal Tunnel

    Points: 4438

    You can use variables in CTEs.

    Below there is a sample you can widely meet in Yukon T-SQL enhancements in SQL Server 2005 books.

    Note that you should use ";" after any sql statement just running before CTE (or With keyword)

     

    Declare @Id as int

    Set @Id = 13;

    With SampleCTE (Id, Unit, ReportstoId, TypeId)

    As

    (

    Select Id, Unit, ReportstoId, TypeId From CompanyUsers Where Id = @Id

    Union All

    Select CompanyUsers.Id, CompanyUsers.Unit, CompanyUsers.ReportstoId, CompanyUsers.TypeId

    From CompanyUsers

    Inner Join SampleCTE On SampleCTE.Id = CompanyUsers.ReportstoId

    )

    Select TypeName, Unit From SampleCTE

    Inner Join Types On Types.Id = SampleCTE.TypeId

    Go

     

    Eralper

    htpp://www.kodyaz.com

     

  • Patrick Emmons-205367

    Valued Member

    Points: 73

    I don't mean to be dense but what is the difference between CTEs and temporary tables?

  • Eralper

    SSCarpal Tunnel

    Points: 4438

    Temporary tables are kept until you drop them or the session is closed. And you can use them repeatedly within the session.

    But CTE is only used by the Select, Insert, Update or Delete statement that comes after the CTE.

    You can see the temporary table created under the tempdb user tables section by using SQL Query Analyzer.

     

  • ewilson10

    SSCarpal Tunnel

    Points: 4205

    Hello. Would it be correct to say that in views CTEs give most of the flexability of temporary tables while temporary tables still have a place in scripts since they can be "forward-referenced"?



    Everett Wilson
    ewilson10@yahoo.com

  • Jeff Moden

    SSC Guru

    Points: 994239

    Guess I didn't know that... how do you forward reference a temp table?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 32 total)

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