Table Variable & Temp Table Difference/Comparison
Comparison of Table Variables and Temp Tables
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]
| Temp Table | Table 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-compiled | An 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 indexes | Whereas 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 Type | Advantages |
|---|---|
| Table Variables | Use 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 Tables | Can 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]