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

Question

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?

Answer 1

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.

Answer 2

Hi,

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