question

Slick84 avatar image
Slick84 asked

Dropping temp tables before ending Sproc

Hi,

If I dont drop temp tables before my stored procedure ends, do the temp tables continue to exist? I thought as soon as the session is finished the temp tables are dropped.

Thanks, Slick

stored-procedurestemporary-table
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

CirqueDeSQLeil avatar image
CirqueDeSQLeil answered

If you create a local temp table, it should drop as soon as the session ends. Globals may persist longer. Occasionally I have seen local temp tables persist after the session ends. It is cleaner and more certain to explicitly drop the temp table.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

Temp tables live past the end of stored procedure execution - they live until the connection in which they were created is disconnected.

Global temp tables (temp tables with double # - CREATE TABLE ##globalTempTable ...) live until all connections that have referenced them are disconnected, irrespective of what happens on the connection that created them.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.