How do I drop table variables in SQL-Server? Should I even do this?
Solution 1
Table variables are automatically local and automatically dropped -- you don't have to worry about it.
Solution 2
if somebody else comes across this... and you really need to drop it like while in a loop, you can just delete all from the table variable:
DELETE FROM @tableVariableName
Solution 3
Table variables are just like int or varchar variables.
You don't 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 the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared.
Solution 4
But you all forgot to mention, that if a variable table is used within a loop it will need emptying (delete @table) prior to loading with data again within a loop.
Solution 5
Just Like TempTables, a local table variable is also created in TempDB. The scope of 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 dropped when you close that session on which you create them.
jtpereyda
Maintainer of boofuzz. "If you're going to make a backward-compatibility-breaking change, no time is better than now; things will be worse in the future." Eric Lippert Sharp Regrets: Top 10 Worst C# Features
Updated on July 08, 2022Comments
-
jtpereyda almost 2 years
I have a table variable in a script (not a stored procedure). Two questions:
- How do I drop the table variable? Drop Table @varName gives an "Incorrect snytax" error.
- Should I always do this? I hear it's a good practice. Is it ever really necessary for small scripts like this?
Here's 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
-
Martin Smith about 13 yearsYou can't drop them yourself so the 2nd part of your question doesn't apply.
-
JNK about 13 years+1 - Also you can't drop them even if you wanted to - they persist as long as the session is open, just like any other variable. They are also unaffected by transactions.
-
dburges about 13 years@JNKs point about them being unaffected by transactions is important, you can use table variables to hold data and write to a log table after an error causes a rollback.
-
Paul Perigny about 13 yearsNo it is not the same. Temp tables participate in transactions.
-
Hogan about 13 yearsNor are they the same as CTEs.
-
R K Sharma over 8 yearsyou can't drop them but you can delete them 'delete @projectList' ;)
-
Hogan over 8 yearsJust to be clear
DELETE @projectList
will remove all rows from the table in table variable@projectList
-
Abou-Emish over 6 years#temp table isn't the same as @table variable, it doesn't drop automatically after the end of patch or scope, it's dropped automatically only if it was created inside stored procedure and stored procedure finished executing
-
StriplingWarrior about 6 yearsDo you know whether the tempdb objects created by table variables get cleaned up when they go out of scope? Or does the server wait until the session is closed before cleaning them up?
-
nitinsridar over 3 yearsbut id will increment, it will not set to 1 again when you declare in loop
-
Rahul Varadkar over 3 yearsThis will delete all rows in Table variable so it is empty for next iteration in the loop. This works for my requirement. Thank you.