How to delete table variables in SQL Server? Should I do this?

Question

I have a table variable in a script (and not a stored procedure). Two questions:

• How to delete a table variable? Drop Table @varName gives the "Invalid snytax" error.
• Should I always do this? I heard that this is a good practice. Is it really necessary for small scripts like this?

Here is my code:

Declare @projectList table( name varchar(40) NOT NULL); Insert Into @projectList Values ('BCR-00021') Select * From @projectList Drop Table @projectList -- does not work 

Answer 1

Table variables are automatically local and automatically discarded - you have nothing to worry about.

Answer 2

Table variables are similar to variables of the int or varchar type.

You do not need to drop them. They have the same scope rules as int or varchar variables

The scope of a variable is the range of Transact-SQL statements that can reference a variable. The volume of a variable lasts from the point that is declared until the end of the batch or stored procedure at which it is declared.

Answer 3

Just Like TempTables, a local table variable is also created in TempDB. The scope of a table variable is the batch, stored procedure, and statement block in which it is declared. They can be passed as parameters between procedures. They are automatically deleted when you close the session in which you create them.

Answer 4

But you all forgot to mention that if a table variable is used in a loop, it will need to clear (delete @table) before loading the data again in the loop.

Answer 5

if someone else comes across this ... and you really need to drop it, as in a loop, you can just delete everything from the table variable:

 DELETE FROM @tableVariableName