Comments on temporary table and Table Variables

-- Comment on temporary table and Table Variables

Note
You can declare functions and variables as table types. Table variables can be used in functions, stored procedures, and batch processing.

Try to use table variables instead of temporary tables. Table variables have the following advantages:

The behavior of table variables is similar to that of local variables and has a clearly defined scope. This scope is the function, stored procedure, or batch processing that declares the variable.
In its scope, table variables can be used as regular tables. This variable can be applied to tables or table expressions in select, insert, update, and delete statements. However, table cannot be used in the following statements:

Insert into table_variable exec stored procedure.

Select select_list into table_variable statement.

When the function, stored procedure, or batch processing of the table variable is defined, the table variable is automatically cleared.

Using table variables in a stored procedure reduces the amount of recompilation in a stored procedure compared to using a temporary table.


Transactions involving table variables only exist during table variable update. This reduces the need for table variables to lock and record resources.
Table variables cannot be assigned values. In addition, because the table variable scope is limited and is not part of the persistent database, it is not affected by transaction rollback.