Article ID: 305977 - View products that this article applies to.
This article was previously published under Q305977
This article answers some of the Frequently Asked Questions (FAQs) related to the table variables that are introduced in SQL Server 2000.
To read the SQL Server Books Online description of table variables, please visit the following Microsoft Web site:
Q1: Why were table variables introduced when temporary tables were already available?
A1: Table variables have the following advantages over temporary tables:
A2: The following article discusses some reasons when stored procedures are recompiled:
243586The "Recompilations Due to Certain Temporary Table Operations" section also lists some requirements to avoid such as a recompilation because of temporary tables. These restrictions do not apply to table variables.
(http://support.microsoft.com/kb/243586/ )Troubleshooting stored procedure recompilation
Table variables are completely isolated to the batch that creates them so no 're-resolution' has to occur when a CREATE or ALTER statement takes place, which may occur with a temporary table. Temporary tables need this 're-resolution' so the table can be referenced from a nested stored procedure. Table variables avoid this completely so stored procedures can use plan that is already compiled, thus saving resources to process the stored procedure.
Q3: What are some of the drawbacks of table variables?
A3: These are some of the drawbacks as compared to temporary tables:
A4: A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).
Q5: Do I have to use table variables instead of temporary tables?
A5: The answer depends on these three factors:
In general, you use table variables whenever possible except when there is a significant volume of data and there is repeated use of the table. In that case, you can create indexes on the temporary table to increase query performance. However, each scenario may be different. Microsoft recommends that you test if table variables are more helpful than temporary tables for a particular query or stored procedure.
Article ID: 305977 - Last Review: October 15, 2012 - Revision: 7.0
Contact us for more help