本文主要是介绍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

 

这篇关于Explicitly drop temp table or let SQL Server handle it的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！