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