lsmith 90637 (1/16/2015)
A word of caution regarding CTEs.I have run into problem with using CTE's against very large tables, especially in the 2008 line of SQL Servers, where the can CTE causes something approaching vapor lock. It gets worse with nested CTE's, in that the execution and re-execution of the queries inside CTE's must be re-executed every time they are referenced. The symptom when seen with SP_WHO2 is the query fails to progress for many minutes, even hours. It consumes CPU, and blows out to a large number of parallelized executions.
Refactoring the same query to replace the CTE with a derived table or dumping the CTE results to a #temp table first, restore the query to sub-minute performance.
It seems that there may have been a defect in 2008R2 that was resolved in 2012. In other words, we could readily reproduce in 2008R2 but not in 2012.
We've removed all use of CTE from our code, except for recursive queries. Even there, we dump the results to a #temp table and use the #temp thereafter.
Sledge hammer to swat a fly.
CTEs are not the problem here. After all they are nothing more than an alternate way to write subqueries (except for recursive CTEs). Use them to make your code neater.
It is true though that in some cases you get better performance with named temp tables. As always, it depends.
Gerald Britton, Pluralsight courses