CTE( common table expression)

Question

Post reply

CTE( common table expression)

sandeep4testing

Mr or Mrs. 500

Points: 534
More actions
March 15, 2012 at 8:36 pm

#391814

Hi dude,
can u tell me what is CTE( common table expression),
how we can use this?.
Thank's
Sandeep.

Viewing 6 posts - 1 through 5 (of 5 total)

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

ColdCoffee

SSC-Dedicated

Points: 39972

March 15, 2012 at 10:25 pm

#1460311

Read here : Common Table Expressions

manub22 Hall of Fame Points: 3163 More actions · Answer 2

CTE or Common Table Expressions were introduced in SQL Server 2005. Their main use is to replace the derived queries or sub-queries you apply in your SQL statements. This also saves us creating multiple sub-queries if required in a SQL statement. Like if your are self-joining 2 same sub-queries, you only need to create a single CTE once and just self-join on its name twice. They also provide us to created recursive queries, thus saving us from creating complex logic and loops.

Check all posts tagged with CTE in following blog: http://sqlwithmanoj.wordpress.com/tag/cte/[/url]

Do check MS BOL: http://msdn.microsoft.com/en-us/library/ms190766.aspx

Also check the links ColdCoffee has provided.

~manoj | LinkedIn | Facebook | YouTube
Blog: SQLwithManoj.com

sandeep4testing Mr or Mrs. 500 Points: 534 More actions · Answer 3

Thank you very much i gain good knowledge with support of you.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

manub22 (3/15/2012)
This also saves us creating multiple sub-queries if required in a SQL statement. Like if your are self-joining 2 same sub-queries, you only need to create a single CTE once and just self-join on its name twice. They also provide us to created recursive queries, thus saving us from creating complex logic and loops.

I'll also mention that those two "features" can cause some pretty nasty performance problems and must be used with some good bit of caution and contemplation.

If you reference a CTE more than once in a query (self join), the CTE will be executed more than once just like a VIEW would. It is sometimes much better to calculate the result of what you'd normally put in a CTE and store it in a Temp Table and then reference the Temp Table more than once.

Recursive CTEs (rCTEs) seem like a God-Send for many problems but they can be the Devil in disguise especially if the purpose of the rCTE is simply to produce a sequence (count) of numbers. Each iteration of the CTE will cause 8 reads (much more than a While Loop) and are just as slow in most cases. Please see the following article for much more detail on the problems with "counting recursive CTEs".

http://www.sqlservercentral.com/articles/T-SQL/74118/

--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.

Change is inevitable... Change for the better is not.

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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

p.s.

The word "dude" isn't always a complimentary one. You might want to avoid it's use.

--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.

Change is inevitable... Change for the better is not.

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