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 ...

Get Microsoft® SQL Server 2008 R2 Unleashed now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.