RSS Feed

Table Variable & Temp Table

Table Variable & Temp Table Difference/Comparison

Temp TableTable Variable
Temp tables behave same as normal tables and are bound by transactions.Table variables are Transaction neutral. They are variables and thus aren't bound to a transaction. transaction logs are not recorded for the table-variables
Any procedure with a temporary table cannot be pre-compiledAn execution plan of procedures with table-variables can be statically compiled in advance
Table-variables exist only in the same scope as variables. Contrary to the temporary tables, they are not visible in inner stored procedures and in exec(string) statements.
A temp table can have indexesWhereas a table variable can only have a primary index.
For smaller tables (less than 1000 rows) use a temp variable Otherwise use a temp table

Comparison of Table Variables and Temp Tables

Temp Table TypeAdvantages
Table VariablesUse less logging and locking resources. Not affected by transaction rollbacks (less work to do). Not subject to recompilation because you can't accidently use a DDL statement to define table variables. Better for smaller intermediary results.
#Temp TablesCan create non-clustered indexes on them for performance. Can use staistics. Can use in sp_ExecutSql statements. Better for large intermediary results.

Table variables have few additional limitations:

Table variables may not be created by means of Select * into or insert into @tablename exec table syntax.

Table variables may not be created within the function.

Table variables are limited in their allowable constraints: no foreign keys or check constraints are allowed. Primary keys, defaults, nulls and unique constraints are ok.

Table variables may not have any dependent objects such as triggers or foreign keys. [Via: SQL Server 2005 Bible By Paul Nielsen]

Few important links on Table Variable & Temp Table

1. Biggest difference between Temp tables and Table variables in Sql Server. [Link]
2. Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance. [Link]
3. What's the difference between a temp table and table variable in SQL Server? [Link]
4. What's the difference between a temp table and table variable in SQL Server? [Link]