EdSwiedler (8/9/2011)
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.
As far as I know you can only create a single column, automatically-named primary key on a table variable (just learned this pretty recently).