Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What are the benefits (if any) of dropping a temporary table in SQL Server?

eg:

DROP TABLE #temp;

Since temporary tables are automatically dropped by the server this statement would seem to be non-functional.

share|improve this question
add comment

4 Answers

up vote 3 down vote accepted

#temp tables that are created in a child scope (Procedure, Trigger, EXEC-ed SQL) are automatically dropped when the scope ends.

But ones at @@NESTLEVEL of 0 are only automatically dropped when the session ends. These hang around for multiple batches.

So for such adhoc queries it is essential to drop them (especially if they might be run multiple times or a later batch will create one of the same name).

Pasting the following into a new query window in SSMS

CREATE TABLE #T (X INT PRIMARY KEY)

INSERT INTO #T VALUES (1)

Will fail on the second run with

Msg 2714, Level 16, State 6, Line 2

There is already an object named '#T' in the database.

share|improve this answer
add comment

I typically drop #temp tables explicitly just out of habit (I like cleaning up anything I create). There is a myth out there that dropping a #temp table explicitly will prevent #temp table caching and reuse, but this is not true: See this blog post by @PaulWhite for a lot more details, and this one for even more.

share|improve this answer
add comment

You're explicitly telling the server to remove references to the #temp table. If the #temp table was using a lot of resources in, for instance, tempdb executing DROP TABLE #temp may reduce resource consumption prior to the end of the session.

You might want to drop the table to ensure it doesn't get erroneously used after its data has been invalidated.

share|improve this answer
    
Interesting, so your saying that a query batch could have an error in it which would be easier to find if you drop the table, of course if the last line of the query is the drop then this is not a benefit. –  Hogan Oct 28 '13 at 19:40
    
Aarons link does not back this up, when you drop a temp table it is just renamed, no resources are freed. "The temporary object is renamed to an internal form when DROP TABLE is executed, and renamed back to the same user-visible name when CREATE TABLE is encountered on the next execution." from sqlblog.com/blogs/paul_white/archive/2012/08/15/… –  Hogan Oct 28 '13 at 19:47
    
@Hogan - That article is talking about cached #temp tables in stored procedures. This is only a subset of temporary tables. –  Martin Smith Oct 28 '13 at 20:58
    
DROP TABLE... on a cached temporary table will still truncate the data and make the name of the table invalid after the DROP statement. The resources (data) stored by the cached table will still be freed even though the actual table definition and associated cached plans are still valid. –  Max Vernon Oct 28 '13 at 21:44
1  
OK, I'm convinced and gave you a +1, but I still think Martin has a more compelling case for an actual realized benefit. –  Hogan Oct 29 '13 at 14:14
show 1 more comment

There is no reason to perform this operation in a query or stored procedure unless you plan on using the same temporary table name later in the session. However, in this case a new name could be used as appropriate for the new functionality.

share|improve this answer
2  
In fact if you try to re-use the same table name in a batch it will fail, so there is no way to drop in that case. –  Aaron Bertrand Oct 28 '13 at 19:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.