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.