Table Variable vs. Temp Table Summary
Feature |
Table Variable |
Temp Table |
Note |
Table Name |
Max 128 characters |
Max 116 characters |
|
Data Storage |
In memory and TempDB |
TempDB |
|
|
In memory |
TempDB |
A table variable inherits current database settings and can use the registered UDTs, user defined data types, and xml collections in the database. A temp table inherits the settings of TempDB and cannot use the types created in the user database if the same types do not exist in the TempDB. |
Scope |
Current batch |
Current session |
Temp tables created in a stored procedure (SP) can be referenced by dynamic queries in the SP, sub SPs, triggers fired by the affected tables of the SP. |
Constraints |
Allowed |
Allowed |
For table variables, since no DDL is allowed, constraints can not be created in separate DDL statements. |
DDL |
Not allowed |
Allowed. |
E.g. create Index on the temp table. |
Concurrent |
Supported |
Supported |
Constraints and Indexes with explicit name in a temp table cause duplicate name error. |
Statistics |
Not supported |
Supported |
Estimated row number in execution plan for table variable is always 1 |
Parallel execution plan |
Supported only for select |
Supported |
Parallel query execution plans are not generated for queries that modify table variables. |
Transaction and Locking |
Not participated |
Participated |
Data in table variable is not affected if the transaction is rolled back |
Cause Recompile |
No |
Yes |
Temp Table creation causes SPs/batches to recompile |
SELECT INTO <t> |
Not supported |
Supported |
|
INSERT <t> EXEC |
Not supported |
Supported |
|
Use |
UDFs, Stored procedures, Triggers, Batches |
Stored procedures, Triggers, Batches |
Temp tables can't be used in UDFs. |