Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Table variable and temporary table

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)

Comments

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.

Leave a Comment

Please register or log in to leave a comment.