• One thing that I have found that can cause a temporary table or table variable to perform better than a CTE is that you can create indexes on them. This will help when joining them to other tables in later processing. No, I have not actually captured statistics on this, but have noticed a perceived performance boost. As with everything SQL Server, this will all be impacted by other tasks running at the same time.

    I will say that I have found that a series of CTE's, each building on the last, does better than the massive join's that I have encountered.