Post reply

common table expressions

  • July 15, 2012 at 1:47 am

    #258987

    Hi

    I have used recursive common table expressions which i really like.

    is there such thing as a non recursive CTE? how are they used?

    how else can a cte be used?

    thanks

  • July 15, 2012 at 5:31 am

    #1512862

    First two examples (in the suggested article below) exhibit non recursive common table expressions (CTE) but CTE is motsly used for recursion.

    WITH common_table_expression (Transact-SQL)

    http://msdn.microsoft.com/en-us/library/ms175972(v=sql.105).aspx

  • July 15, 2012 at 9:58 am

    #1512877

    I use CTE's to organize my queries and move derived tables up to the top of the query. In other cases, I will use a CTE to include row_number, rank, dense_rank or computed columns to be later used in calculations.

    I have also converted temp tables to CTEs for SSIS/SSRS procedures since both tend to have significant issues with temp tables. In some cases, moving to CTEs instead of temp tables improved performance - in other cases it didn't.

    Generally, I will take the performance hit in SSIS/SSRS over the requirements to allow either to use temp tables. However, if the performance is really bad - I will use temp tables and work around the limitations.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • July 16, 2012 at 2:01 am

    #1512943

    thanks for the replies

    interesting stuff 🙂

  • August 7, 2012 at 4:37 am

    #1522490

    Jeffrey Williams 3188 (7/15/2012)

    I use CTE's to organize my queries and move derived tables up to the top of the query. In other cases, I will use a CTE to include row_number, rank, dense_rank or computed columns to be later used in calculations.

    I have also converted temp tables to CTEs for SSIS/SSRS procedures since both tend to have significant issues with temp tables. In some cases, moving to CTEs instead of temp tables improved performance - in other cases it didn't.

    Generally, I will take the performance hit in SSIS/SSRS over the requirements to allow either to use temp tables. However, if the performance is really bad - I will use temp tables and work around the limitations.

    I have done exactly this today and i like it

    the query does seem a bit slow tho, do CTEs have bad performance?

  • August 7, 2012 at 8:46 am

    #1522646

    erics44 (8/7/2012)

    Jeffrey Williams 3188 (7/15/2012)

    I use CTE's to organize my queries and move derived tables up to the top of the query. In other cases, I will use a CTE to include row_number, rank, dense_rank or computed columns to be later used in calculations.

    I have also converted temp tables to CTEs for SSIS/SSRS procedures since both tend to have significant issues with temp tables. In some cases, moving to CTEs instead of temp tables improved performance - in other cases it didn't.

    Generally, I will take the performance hit in SSIS/SSRS over the requirements to allow either to use temp tables. However, if the performance is really bad - I will use temp tables and work around the limitations.

    I have done exactly this today and i like it

    the query does seem a bit slow tho, do CTEs have bad performance?

    No a CTE does not have bad performance anymore than any query has bad performance. It is HOW the query is written that causes poor performance.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • August 7, 2012 at 10:02 am

    #1522697

    I'm like Jeff.

    I use CTE's to move subqueries to the top so I can treat them as the proper named derived tables that they are.

    I also use them when I need to use things like ROW_NUMBER in a where clause (you can't use it directly in a where clause but if it's in a CTE you can use it in the query that makes use of it).

    Of the hundreds of CTE's I've written, perhaps 1-2 of them make use of recursion.


    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • August 7, 2012 at 10:24 am

    #1522713

    I use CTEs to breakdown development as well. Makes the code cleaner when the CTE is used instead of a derived table in the FROM clause, and it makes using the windowing functions (ROW_NUMBER(), RANK() , etc) easier as well.

Viewing 8 posts - 1 through 7 (of 7 total)

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