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



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


4 Answers 4


#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



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.


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.


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.

  • 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, 2013 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, 2013 at 19:47
  • @Hogan - That article is talking about cached #temp tables in stored procedures. This is only a subset of temporary tables. Oct 28, 2013 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.
    – Hannah Vernon
    Oct 28, 2013 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, 2013 at 14:14

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.

  • 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. Oct 28, 2013 at 19:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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