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.

---

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.

---

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.

---

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.