Table variables and Temporary tables are used interchangeably – but they were designed for different purposes.
Temporary tables were designed for storage and manipulation of temporal data.
Table variables were designed to return User Defined Function (UDF) datasets.
Which type should be used ?
If I estimate the data sets are smaller than 75,000 and no SQL Server indexes are required Table variables are worth investigating. Always test both scenarios as part of design and acceptance testing.
Consider SQL Server test data generation testing tools and then run some artifical queries for performance testing.
Feature comparison of Table variable and Temporary Table
Feature | Table variable | Temporary table |
Constraint type | Primary Key, Unique Key , NULL | All except Foreign Key |
Parallel execution plans while modifying table | No | Yes |
Parallel execution plans while read-only | Yes | Yes |
SELECT,INSERT,UPDATE ,DELETE | Yes | Yes |
Indexes | No (except Clutsered Index) | Yes |
Statistics | No | Yes |
Assignment operation b/w variable | No | Yes |
Affected by transaction rollbacks | No |
|
Partitioning | No | No |
Stored in sysobjects | No | Yes |
Instantiated in TempDB | Yes | Yes |
Altered by DDL | No | Yes |
DML statement scope | 1 | multiple |
|
|
Author: Jack Vamvas (http://www.sqlserver-dba.com)
