CTE

Question

CTE

williams george

SSC Rookie

Points: 45
More actions
September 30, 2011 at 10:55 pm

#390521

what is CTE. How to use it

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

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

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

Mighty broad topic for a forum. I would suggest starting with SQL Server Books Online and read what it has to say about CTEs. After that you should be able to ask questions that are much more targeted to specific areas where you need more clarification.

vince_sql Right there with Babe Points: 722 More actions · Answer 2

A CTE allows you to encapsulate a result set of a query and run queries against it. The result set within a CTE is temporary and exists only in the context of the query which is using it.

It can be useful for recursive queries, filtering based on ranking functions etc.

Check out the books online page for more info: http://msdn.microsoft.com/en-us/library/ms190766.aspx

http://sqlvince.blogspot.com/[/url]

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

Here's the documentation on Common Table Expressions.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

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

A CTE is really nothing more than a "Derived Table" (sub-SELECT) which you would find in a FROM clause whose result set is being used as a table.

The two things you can do with a CTE that you can't do with a Derived Table is...

1. You can't "call" a Derived Table more than once (ie, give it more than one "table alias").

2. You can't write a recursive Derived Table.

Notice that I DIDN'T call those two features "advantages" because, although quite convenient for the programmer, they're usually terrible for performance and resource usage.

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