If you look at the query plan often the cte will be evaluated multiple times. It is more like a table variable than a temp table. The table variable needs to be defined then populated whereas the cte can get the definition from the query - in that way it is like a select into which can only be used with a temp table not a table variable but it doesn't have the overhead of a temp table.
CTE also allows recursion Also you can define multiple ctes to use the result of previous ones which is quicker to code than other tables.
I use cte's a lot even when recursion is not needed as they are quicker to code and don't take up resources after the statement (hopefully). Anything that is needed for multiple statements I will use a table variable or temp table depending on size and indexing needs.
========================================== Cursors are useful if you don't know sql. SSIS can be used in a similar way. Beer is not cold and it isn't fizzy.
A CTE is little more than a named subquery or temporary view. As part of query parsing, it'll be inlined into the query. A temp table, like a table variable, is an actual table created and stored in TempDB
Difference between CTE and temp table
Posted - 05/25/2011 : 05:26:30
