Microsoft® SQL Server 2008 R2 Unleashed

Temp Tables Versus Table Variables Versus Common Table Expressions

SQL Server 2008 provides multiple options for working with temporary result sets in T-SQL code:

• Temporary tables

• Table variables

• Derived tables

• Common table expressions

One of the questions you may consider is “Which method should I use and when?” Whether you use a temporary table, table variable, derived table, or common table expression depends, in part, on how often and for how long you intend to use it. This section provides some general recommendations to consider.

You should use table variables instead of temporary tables in stored procedures whenever possible or feasible. Table variables are memory resident and do not incur the I/O overhead and system table and ...