Cleaning up after #temp

[Andy Dufresne]

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]

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]

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.

[Babu M]

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?