common table expressions

Question

common table expressions

erics44

SSCertifiable

Points: 6311
More actions
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

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

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

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

erics44 SSCertifiable Points: 6311 More actions · Answer 3

erics44

SSCertifiable

Points: 6311

July 16, 2012 at 2:01 am

#1512943

thanks for the replies

interesting stuff 🙂

erics44 SSCertifiable Points: 6311 More actions · Answer 4

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?

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

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/

mtassin SSC-Insane Points: 23099 More actions · Answer 6

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]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

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.