SqlServer教程

Explicitly drop temp table or let SQL Server handle it

本文主要是介绍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的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!