How do I drop table variables in SQL-Server? Should I even do this?

Question

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

You can't drop them yourself so the 2nd part of your question doesn't apply. — Martin Smith, Apr 13 '11 at 18:02

score 233 · Accepted Answer · answered Apr 13 '11 at 18:01

233

Table variables are automatically local and automatically dropped -- you don't have to worry about it.

answered Apr 13 '11 at 18:01

Hogan

69,564
10
76
117

22

+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. – JNK Apr 13 '11 at 18:04
11

@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. – HLGEM Apr 13 '11 at 18:08
3

No it is not the same. Temp tables participate in transactions. – Paul Perigny Apr 13 '11 at 18:49
Nor are they the same as CTEs. – Hogan Apr 13 '11 at 20:18
3

you can't drop them but you can delete them 'delete @projectList' ;) – R K Sharma Jan 22 '16 at 06:36
Just to be clear `DELETE @projectList` will remove all rows from the table in table variable `@projectList` – Hogan Jan 22 '16 at 15:36

score 45 · Answer 2 · answered Aug 02 '17 at 08:28

45

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

answered Aug 02 '17 at 08:28

Leo Much

629
5
6

6

but id will increment, it will not set to 1 again when you declare in loop – nitinsridar Nov 15 '20 at 15:02
This will delete all rows in Table variable so it is empty for next iteration in the loop. This works for my requirement. Thank you. – Rahul Varadkar Nov 22 '20 at 09:26

score 32 · Answer 3 · answered Apr 13 '11 at 18:04

32

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.

answered Apr 13 '11 at 18:04

gbn

422,506
82
585
676

1

Do 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? – StriplingWarrior Jun 04 '18 at 22:42

score 12 · Answer 4 · answered Apr 27 '17 at 21:11

12

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.

answered Apr 27 '17 at 21:11

Peppe

121
1
4

score 3 · Answer 5 · edited Feb 22 '17 at 01:00

3

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.

edited Feb 22 '17 at 01:00

Sullify

52
8

answered May 15 '15 at 07:01

Gagan

93
3

#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 – Abou-Emish Oct 18 '17 at 18:52

score 1 · Answer 6 · edited Sep 30 '20 at 08:06

1

Temp table variable is saved to the temp.db and the scope is limited to the current execution. Hence, unlike dropping a Temp tables e.g drop table #tempTable, we don't have to explicitly drop Temp table variable @tempTableVariable. It is automatically taken care by the sql server.

drop table @tempTableVariable -- Invalid

edited Sep 30 '20 at 08:06

alexherm

1,362
2
18
31

answered Sep 29 '20 at 21:20

Rubysmita Seth

21
1

score 0 · Answer 7 · answered Jan 12 '21 at 17:15

Indeed, you don't need to drop a @local_variable.

But if you use #local_table, it can be done, e.g. it's convenient to be able to re-execute a query several times.

SELECT *
INTO #recent_records
FROM dbo.my_table t
WHERE t.CreatedOn > '2021-01-01'
;

SELECT *
FROM #recent_records
;

/*
  can DROP here, otherwise will fail with the following error
  on re-execution in the same window (I use SSMS DB client):

  Msg 2714, Level ..., State ..., Line ...
  There is already an object named '#recent_records' in the database.
*/
DROP TABLE #recent_records
;

You can also put your SELECT statement in a TRANSACTION to be able to re-execute without an explicit DROP:

BEGIN TRANSACTION

  SELECT *
  INTO #recent_records
  FROM dbo.my_table t
  WHERE t.CreatedOn > '2021-01-01'
  ;

  SELECT *
  FROM #recent_records
  ;

ROLLBACK

score -1 · Answer 8 · answered Apr 11 '19 at 08:16

Here is a solution

Declare @tablename varchar(20)
DECLARE @SQL NVARCHAR(MAX)

SET @tablename = '_RJ_TEMPOV4'
SET @SQL = 'DROP TABLE dbo.' + QUOTENAME(@tablename) + '';

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@tablename) AND type in (N'U'))
    EXEC sp_executesql @SQL;

Works fine on SQL Server 2014 Christophe

How do I drop table variables in SQL-Server? Should I even do this?

8 Answers8

Linked