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

sql-server temp-tables
Jeff picture Jeff · Mar 27, 2009 · Viewed 46.3k times · Source

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

John Sansom picture John Sansom · Mar 27, 2009

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

Related questions

Check if a temporary table exists and delete if it exists before creating a temporary table

I am using the following code to check if the temporary table exists and drop the table if it exists before creating again. It works fine as long as I don't change the columns. If I add a column later, …

Read More
Inserting data into a temporary table

After having created a temporary table and declaring the data types like so; CREATE TABLE #TempTable( ID int, Date datetime, Name char(20)) How do I then insert the relevant data which is already held on a physical table within the …

Read More
What's the difference between a temp table and table variable in SQL Server?

In SQL Server 2005, we can create temp tables one of two ways: declare @tmp table (Col1 int, Col2 int); or create table #tmp (Col1 int, Col2 int); What are the differences between these two? I have read conflicting opinions on …

Read More