Follow by Email

Friday, March 29, 2013

Table variable and Temp table

Difference between table variable and temp table :

Table Variable Temp Table
1 It cannot be rollbacked.

It can be rollbacked.
2 It gets destroyed after the particular code gets executed(Like Stored procedures) It uses tempdb to store the temp tables. Thus it can be accessed till the sql connection exists

3 Less Recompilation

More Recompilation
4 It can only have cluster index on the table It can have both cluster and non cluster indexes
5 It can be used with Stored procedures, Functions, Triggers.

It can be used with Stored procedures, Triggers.
6 It Should be used when there is no transaction  to be used for table variables. It can be used within the transaction block.

Examples:

Table variable :


Temp Variable: