Explicitly drop temp table or let SQL Server handle it

Explicitly drop temp table or let SQL Server handle it

My view is, first see if you really need a temp table - or - can you make do with a Common Table Expression (CTE). Second, I would always drop my temp tables. Sometimes you need to have a temp table scoped to the connection (e.g. ##temp), so if you run the query a second time, and you have explicit code to create the temp table, you'll get an error that says the table already exists. Cleaning up after yourself is ALWAYS a good software practice.

EDIT: 03-Nov-2021

Another alternative is a TABLE variable, which will fall out of scope once the query completes:

DECLARE @MyTable AS TABLE (
    MyID INT, 
    MyText NVARCHAR(256)
)

INSERT INTO
    @MyTable
VALUES
    (1, 'One'),
    (2, 'Two'),
    (3, 'Three')

SELECT
    *
FROM
    @MyTable

 

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

问题1：

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

 

+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

 

 

问题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

 

作者:Chuck Lu    GitHub