Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 392
Jeff Avatar asked Mar 27 '09 04:03

Jeff


4 Answers

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.

like image 32
vezult Avatar answered Sep 30 '22 14:09

vezult


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
like image 152
John Sansom Avatar answered Sep 30 '22 14:09

John Sansom


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

like image 37
PaulStock Avatar answered Sep 30 '22 15:09

PaulStock


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;
like image 24
BassGod Avatar answered Sep 30 '22 15:09

BassGod