|
Use table variables instead of temporary tables whenever possible. The table variable has the following advantages:
Table variables behave like local variables, with well-defined scope. The scope is the function, stored procedure, or batch that declares the variable.
Within its scope, table variables can be used like regular tables. This variable can be used in SELECT, INSERT, UPDATE, and DELETE statements where a table or table expression is used. However, table cannot be used in the following statements:
INSERT INTO table_variable EXEC stored procedure.
SELECT select_list INTO table_variable statement.
At the end of the function, stored procedure, or batch that defines the table variable, the table variable is automatically cleared.
The use of table variables in stored procedures reduces the amount of recompilation of stored procedures compared to the use of temporary tables.
Transactions involving table variables only exist during table variable updates. This reduces the need for table variables to lock and record resources.
Assignment operations between table variables are not supported. In addition, because table variables have limited scope and are not part of a persistent database, they are not affected by transaction rollbacks. |
|