Blog Post

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)


Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating