so for example. I have the below. Is this the same basic principle as using an into #tablename and then querrying it? Are the CTE's specific to the runtime of a stored procedure or once their defined thay can be refferenced forever?

BaldingLoopMan (12/8/2009)so for example. I have the below. Is this the same basic principle as using an into #tablename and then querrying it? Are the CTE's specific to the runtime of a stored procedure or once their defined thay can be refferenced forever?

Seriously? You can only use a CTE in the statement immediately following?When I read the article, the impression I got was that you could define a CTE at the top of something like a stored proc, then use it all the way through. Is my understanding wrong?

niall.baird (12/8/2009)Seriously? You can only use a CTE in the statement immediately following?When I read the article, the impression I got was that you could define a CTE at the top of something like a stored proc, then use it all the way through. Is my understanding wrong?

Lynn Pettis(12/8/2009)They can only be referenced by the immediately following SELECT/INSERT/UPDATE/DELETE statement.

I *can* read Garadin. That is why I was questioning the above statement. Seems to me that defining a #table would be more use, as you can use it in more places - unless of course, I am missing something.

In SQL Server 2000, at a previous employer, I wrote several queries that used the same derived table several times in a single select statement. It was a royal pain to write, and when the derived table had to be modified it had to be modified in several places. Enter SQL Server 2005 and CTE's. Now, just before the select statement, I can define to "derived table" once and use it several times, just like a table in the select statement. If the CTE needs to be modified, one place to make the change.

CTE's are very useful when used properly. Sometimes a temporary table is a better option in a stored proc.

niall.baird (12/8/2009)I *can* read Garadin. That is why I was questioning the above statement. Seems to me that defining a #table would be more use, as you can use it in more places - unless of course, I am missing something.

heh, I couldn't resist.

Seriously though, don't take Lynn's word for it, run up a quick test and see for yourself.

They see a ton of usage even with their limitations. They *can* also incur less overhead than creating a temp table, depending on your usage.

All very useful info guys. Once again it appears the geniuses at sql server have designed a new way to do the same thing but w/ different syntax. Sure there are some minor performance gains as usual. Sorry for the disgruntled attitude. The gators lost, I just got in, and haven’t finished my coffee. Also I just ate about 2 lbs of bacon at an all u can eat breakfast buffet. Bacon is my weakness and I’m paying for it now.

Personally i develop using the #tables then convert them to @tables where needed when moving my code to prod. I like the #tables in development because i can see what is in them after the process runs and so on. It's essential to the dev process.

Perhaps i will start to integrate these CTE's going forward and be a good boy.