Common Table expressions

Question

Common Table expressions

Jules Bonnot

SSCertifiable

Points: 5488
More actions
August 1, 2006 at 5:07 am

#113119

Is it the case that you can only reference these in the satement immediatly after their definition.
Didnt microsoft claim that these would make code more modular, easier to read and less dependent on Temp DB.
Obviously if you can only reference them in the statement directly after they cant be reused in a sp and are therefore fairly useless (except for in recursive queries)
www.sql-library.com[/url]

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

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

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

Why can't they be re-used? If needs be, Truncate or Delete them, (I am not sure if Deleting a @TableVariable still fills the Trans Log - it may also cause permission problems for your client).

I wasn't born stupid - I had to study.

Eddie Wuerch SSChampion Points: 12536 More actions · Answer 2

Jules,

Common Table Expressions (CTEs) are new to SQL Server 2005, this might be a more lively thread over in one of the 2005 forums.

From BOL:"A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query."

So, yes, it is only good for a single query. However, they are far from useless, especially if you are writing a query with multiple derived tables that must join to each other. You can define the different derived tables using CTEs before the query itself, and reference already-defined CTEs when defing further CTEs. That allows you to build a multiple-derived-table query one JOIN at a time:

Define first CTE with the query for the first derived table

Define second CTE as the query for the second derived table joined to the first CTE

Define third CTE....

or, just define them separately and join them up in the query.

Building messy queries this way really cleans them up and clarifies what is going on, which is quite handy when you go back to work with it later. If you check out either of the two developer Inside SQL Server 2005 books, you'll see many examples of how this clarifies otherwise difficult-to-read queries.

-Eddie

Eddie Wuerch
MCM: SQL

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

OMG! Thanks Eddie!

We just started using 2005 and I completely misinterpretted the question. We are only beginning to use these, so that information was extremely timely!

Thanks

I wasn't born stupid - I had to study.