Discussion:
Cleaning up after #temp
(too old to reply)
Andy Dufresne
2013-09-09 09:21:07 UTC
Permalink
From my understanding, the following query creates a temporary table in MSSQL 2008 R2:

select col_x, col_y, col_z
into #temp
from sometable

but what happens to that #temp table after I'm done with the query? Does it stay in the database indefinately until it's manually removed? Do I have to do something to clean it up?
Bob Barrows
2013-09-09 10:53:50 UTC
Permalink
Post by Andy Dufresne
select col_x, col_y, col_z
into #temp
from sometable
but what happens to that #temp table after I'm done with the query?
Does it stay in the database indefinately until it's manually
removed? Do I have to do something to clean it up?
1. It is automatically deleted at the end of the session.
2. No
3. Only if you have database connections that are pooled so that multiple
users can share the same session. In this scenario, it might make sense to
explicitly drop it when finished with it, or, when creating it, check for
its existence first and drop it.
Erland Sommarskog
2013-09-09 13:52:57 UTC
Permalink
Post by Andy Dufresne
but what happens to that #temp table after I'm done with the query? Does
it stay in the database indefinately until it's manually removed? Do I
have to do something to clean it up?
A temp table is dropped when you exit the scope it was create in. If you
create it from the top-level scope, for instance directly in a query window,
the table is dropped when you disconnect.

Note that the temp table does not exist in your database, but in tempdb.
--
Erland Sommarskog, SQL Server MVP, ***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Babu M
2013-10-09 20:12:04 UTC
Permalink
Post by Andy Dufresne
select col_x, col_y, col_z
into #temp
from sometable
but what happens to that #temp table after I'm done with the query? Does it stay in the database indefinately until it's manually removed? Do I have to do something to clean it up?
Loading...