Mladen Prajdić Blog

Blog about stuff and things and stuff. Mostly about SQL server and .Net

A bit about sql server's local temp tables

I've seen a lot of confusion about the scope and lifetime of temporaray tables.
While i think that ##global temp tables are clear, #local temp tables are a different story.
They seen to sometimes work and sometimes not, especially when used with dynamic sql.

So what is the problem?
Mostly basic misunderstanding.
So because i'm such a nice guy, i'll try to elaborate a bit about "them darn #temps". :)

BOL clearly states:

Local temporary tables are visible only in the current session… … Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE
So how is scope/session defined?

Scopes/Sessions are an interesting thing in sql server.
They follow the rule of inheritance, same as transactions:
A child has all parents' "objects" plus it's own.
So what this means is that you can access a #temp table created
in the parent process from the child of that parent,
but not vice versa.
Any dynamic SQL execution with either exec() or sp_executeSQL is ran in
a child session/scope of the current one.
Let's try it with some examples and explanations below the code:

 


– 1st example
– this fails
use northwind
exec('select top 5 orderId, customerId into #temp from orders; select * from #temp')
select * from #temp
drop table #temp
The #temp table gets created and queried once as seen in results panel.
Then we have an error message in messages panel informing us:
Invalid object name '#temp'.

We see that we created the #temp table in the child session,
so of course we can't drop it in the parent session, because a temporary table gets
dropped when it goes out of session/scope which happens after exec() finishes it's execution


– 2nd example
– this works
use northwind
select top 5 orderId, customerId into #temp from orders
exec('select * from #temp')
select * from #temp
drop table #temp
The #temp table gets created and queried twice as seen in results panel.
Here the #temp table is created in the parent session so the child can access it freely.
The temp table gets dropped when the parent session end or in this case drop table is called.


– 3rd example
– this works
use northwind
create table #temp
(
    orderId int,
    customerId varchar(5)
)

insert into #temp select top 5 orderId, customerId from orders

exec ('select * from #temp') select * from #temp drop table #temp

This one is same as the 2nd example with the difference of explicitly creating the table.

Legacy Comments


rockmoose
2006-11-04
re: A bit about sql server's local temp tables
Good post, thanks for the clarifications.

Dave Goboff
2006-11-06
re: A bit about sql server's local temp tables
Have you explored DTS packages and temp tables. It seems that if you create a temporary table in a DTS package and then attempt to create a table with the same name in another step, it will bomb. However you can do the exact same steps in windows of TSQL sessions opened through Enterprise Manager. This has bitten me a couple of times, when I want to create essentially similar queries within the DTS package.
dg


2006-11-06
re: A bit about sql server's local temp tables
i prefer not to explore DTS packages :))

but i would think that well all steps are executed on the same connection that's open throughout the package execution. so #temp tables don't get auto-dropped.
I could be wrong of course. I don't have that much experience with DTS.

Nick Fotopoulos
2006-11-30
re: A bit about sql server's local temp tables
I can't seem to get a global temp table (##temptable) to be seen outside of the connection it was created in. From what I have read in DTS the following scope rules apply to temp tables:

local temp
#temptable
is only availble to steps using the same connection

global temp
#globaltemp
is available to all connections

Anyone know if this is wrong?

Vikas
2007-03-20
re: A bit about sql server's local temp tables
good

david
2007-05-22
re: A bit about sql server's local temp tables
what is the scope for global temp tables??? like when are parent and child sps used...

Mladen
2007-05-22
re: A bit about sql server's local temp tables
global temp tables have global scope.
what don't you understand?

they exists as long as at least one connection
that uses the global temp table is open.

Dorchester Software
2007-06-03
re: A bit about sql server's local temp tables
Is there an easy way to create a temporary table with "Connection Scope" - I was thinking this was the same as "Session Scope" but trying it on SQL 2005 seems like its not.

That is I want a temporary table that is specific to the current ADO net connection - from the current client. As long as I keep that connection open I want the temporary tables to persist, and be visible to subsequent calls against the same connection object. Temporary tables should not be visible to connections from other clients or even multiple connections from the same client. So Global scope is no good because connections from other clients will interfere.

But local scope doesn't seem to work either, because as soon as the current sp finishes executing, the temporary tables created in it are automatically destroyed.

Could use brute force and have my own ID system but thought it was a bit strange I would have to do this... am I missing something?

Dorchester Software
2007-06-04
re: A bit about sql server's local temp tables
Hmmm. Ok. I checked the documentation, and sure enough Books online is telling me that local temporary tables are indeed kept in existence as long as the connection is kept open. ie. Session does indeed = Connection. This was actually what I thought in first place.

And yet I have a situation where I create a temporary table in one stored procedure, and keep the ADO net connection open, and then try to access that temporary table in another stored procedure and I am getting the exception raised "Invalid Object Name '#TemporaryTable' " . When I execute the same SQL using a query window making separate calls, but keeping the connection open by keeping the window open it works fine.

anonymus
2007-09-18
re: A bit about sql server's local temp tables
When using a single connection object for multiple ADOtable / ADOcommand objects when a table is opened (and not closed) and after that the command object is used to create a temptable it does not always work.

This is because while another object is using the connection it somehow cant be used for the command object, and so it temporarely creates its own connection. Which will be closed right after execution is complete..

So be realy carefull with using one connection object with multiple adotable/adodataset/adocommand objects becouse if kept open they could change the duration temptables are valid.

Im using Delphi btw.

Mladen
2007-09-18
re: A bit about sql server's local temp tables
well then that's not using a single connection, is it?

Dave Durose
2008-08-15
re: A bit about sql server's local temp tables
I am running into the exact problem that "Dorchester Software" was experiencing. What is the workaround? I create a temporary table in one stored procedure, and keep the ADO net connection open, and then try to access that temporary table in another stored procedure and I am getting the exception raised "Invalid Object Name '#TemporaryTable' ". Thanks!

Mladen
2008-08-15
re: A bit about sql server's local temp tables
temorary tables get destroyed when store procedure exists if they are created in that stored procedure.

Radyo Gelkeyfim
2009-07-20
re: A bit about sql server's local temp tables
Have you explored DTS packages and temp tables. It seems that if you create a temporary table in a DTS package and then attempt to create a table with the same name in another step, it will bomb. However you can do the exact same steps in windows of TSQL sessions opened through Enterprise Manager. This has bitten me a couple of times, when I want to create essentially similar queries within the DTS package.
dg

Yuriy
2010-01-21
re: A bit about sql server's local temp tables
About Delhi and '#TemporaryTable'. That was my problem for "shlong shlong" time. Thanx for your post. It didn't solve my problem but put me on a right track. Solution is simple: don't use parametrize query when creating '#TemporaryTable'.
And voilà!-a

Peter
2010-08-04
re: A bit about sql server's local temp tables
Great post. But in Visual Web Developer problem remains when u use Sqldatasources. Online no problems so far. It is a little annoying when i am developing.