Jeffrey Williams 3188 (7/15/2012)
I use CTE's to organize my queries and move derived tables up to the top of the query. In other cases, I will use a CTE to include row_number, rank, dense_rank or computed columns to be later used in calculations.
I have also converted temp tables to CTEs for SSIS/SSRS procedures since both tend to have significant issues with temp tables. In some cases, moving to CTEs instead of temp tables improved performance - in other cases it didn't.
Generally, I will take the performance hit in SSIS/SSRS over the requirements to allow either to use temp tables. However, if the performance is really bad - I will use temp tables and work around the limitations.
I have done exactly this today and i like it
the query does seem a bit slow tho, do CTEs have bad performance?