CODEWITHSUNDEEP
sql-servertemp-tables
Jeff
Jeff

Reputation: 8138

Deleting Global Temporary Tables (##tempTable) in SQL Server

Does SQL server automatically purge these out after a given length of inactivity or do I need to worry about purging them automatically? If so, how do I query for a list of tables to purge?

Upvotes: 23

Views: 53704

Answers (4)

BassGod
BassGod

Reputation: 191

Since most viewers are here via search engines for the answer to the title of the question: To delete Global Temporary tables in SQL Server 2016 and higher:

DROP TABLE IF EXISTS ##tempTable;

Upvotes: 1

vezult
vezult

Reputation: 5243

Local temporary tables are destroyed when you close your connection to SQL Server. There is no need to manually purge them under normal circumstances. If you maintain a persistent connection, or connection pooling, you may want to get in the habit of dropping temporary tables immediately after use.

Global temporary tables, on the other hand, since they are visible to all users in a given database, are destroyed along with the last connection which references them.

Upvotes: 17

PaulStock
PaulStock

Reputation: 11263

In answer to the second part of your question, to query for the list of tables to purge, try this query:

SELECT * FROM tempdb..sysobjects

That will list out all the current global temporary tables

Upvotes: 4

John Sansom
John Sansom

Reputation: 41819

In spite of the fact that temporary tables are dropped when a connection terminates, it is often still considered good practice to explicitly delete such tables in order to be absolutly certain clean up has taken place.

For example, if your platform makes use of connection pooling, i.e. the connections may never be dropped, then do your temporary tables therefore too still exist?

In order to check for the existence of a temporary table you can use the following statement/check.

if object_id('tempdb..##temptbl') is not null
begin
    drop table ##temptbl
end

Upvotes: 27

Related Questions