Super simple question for database novices

timogen

Statements in stored procedures:
declare @t table
(
fid int,
child_id int
)
The table is defined. Do I need to delete it after using it? How can I delete it?

fanfan1980

Do not delete, it is a table variable

我是杀人狂

NO delete ~~ Auto over

fanfan1980

Outside the scope of action, the table variable no longer exists

恶魔右指

This is a table variable is not saved
Same meaning as variable

kittast

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.