I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

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.

Print | posted on Friday, November 03, 2006 10:15 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: A bit about sql server's local temp tables

Good post, thanks for the clarifications.
11/4/2006 10:06 AM | rockmoose
Gravatar

# 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
11/6/2006 6:45 PM | Dave Goboff
Gravatar

# 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.
11/6/2006 6:59 PM |
Gravatar

# 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?
11/30/2006 6:48 PM | Nick Fotopoulos
Gravatar

# re: A bit about sql server's local temp tables

good
3/20/2007 6:11 AM | Vikas
Gravatar

# 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...
5/22/2007 8:59 AM | david
Gravatar

# 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.
5/22/2007 11:17 AM | Mladen
Gravatar

# 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?
6/3/2007 6:36 PM | Dorchester Software
Gravatar

# 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.
6/4/2007 1:06 AM | Dorchester Software
Gravatar

# 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.
9/18/2007 9:28 AM | anonymus
Gravatar

# re: A bit about sql server's local temp tables

well then that's not using a single connection, is it?
9/18/2007 10:05 AM | Mladen
Gravatar

# 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!
8/15/2008 8:57 PM | Dave Durose
Gravatar

# 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.
8/15/2008 9:04 PM | Mladen
Gravatar

# 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
7/20/2009 9:35 AM | Radyo Gelkeyfim
Gravatar

# 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
1/21/2010 5:12 PM | Yuriy
Gravatar

# 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.
8/4/2010 11:45 AM | Peter
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET