• This raises a couple of points.

    First, ditto what everyone else said about the @table variables. For me, the most important thing about them, and the thing that is most often overlooked, is that @tables live outside transaction control. This can have serious consequences.

    Second, derived tables and CTEs are not tables in any sense. They are better thought of as temporary views. I believe that in some cases the optimizer may choose to use tempdb to store the data generated by a derived table or CTE, but this does not make them tables any more than it makes the results of a subquery a table.

    Third, I am not aware of any performance issues with #temp tables under SQLServer2K5. For large result sets, #temp tables have some distinct advantages, as someone else mentioned, and you can create indexes on them, which is a major advantage.