July 15, 2012 at 1:47 am
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
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
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
thanks for the replies
interesting stuff 🙂
August 7, 2012 at 4:37 am
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
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
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.
August 7, 2012 at 10:24 am
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