Subscribe to this blog
Briefcase
Print
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)
Posted by Michael Garstin-341427 on 26 December 2011
"If...data sets are smaller than 75,000..."
What a completely arbitrary number! Without statistics, the Query Optimizer can only guess at the number of rows. Personally, I'd never use a table variable for more than a few dozen rows.
Far too often, I've seen horrible plans come from table variables that - expected or unexpected - contain thousands (and sometimes even millions) of rows.