CODEWITHSUNDEEP
sqlsql-servert-sql
Kenny Evitt
Kenny Evitt

Reputation: 9801

Any benefit to explicitly dropping local temporary tables at the end of a stored procedure?

Consider the following psuedo T-SQL code (performed by a stored procedure):

CREATE TABLE #localTable ...

<do something with the temporary table here>

DROP TABLE #localTable;

The DROP TABLE statement is the last statement executed by the stored proceudre – is there any benefit to that statement?

Note that I'm not asking about dropping temporary tables (local or not) in the middle of the stored procedure (i.e. after the tables are no longer needed, but before the end of the stored procedure code) – that could seemingly have important benefits due to decreasing the memory required to continue executing the stored procedure. I want to know whether there's any benefit (or any effect, really, positive or negative) to explicitly dropping the table versus 'letting' SQL Server do so when the stored procedure finishes executing.

Upvotes: 18

Views: 6010

Answers (3)

Samith C Valsalan
Samith C Valsalan

Reputation: 139

Dropping Temp table is a good habit otherwise that consume space in TEMP DB after our operation.

That will cause space issue . This will get cleared only you shrink TEMP DB or restart the server.

Upvotes: 0

rageit
rageit

Reputation: 3601

Theres a good detailed post on this here.

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. In addition, any statistics that were auto-created on the temporary table are also cached. This means that statistics from a previous execution remain when the procedure is next called.

Upvotes: 8

Randy Minder
Randy Minder

Reputation: 48452

Won't hurt to do so, but the table gets dropped when the connection is dropped. I personally think it's a good habit to get into. It also lets developers, who might have to work on this, that you didn't simply forget to do it.

Upvotes: 7

Related Questions