Showing posts with label table variable. Show all posts
Showing posts with label table variable. Show all posts

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: