Difference between table variable and temp table :
Temp Variable:
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:
No comments:
Post a Comment