SQL Server 2005 and temporary table scope

Question

I've read around the subject of temporary tables and scope and all the answers i've seen don't seem to talk about one of my concerns.

I understand that a local temporary table's scope is only valid withing the lifetime of a stored procedure or child stored procedures. However what is the situation with regard to concurency. i.e. if i have a stored procedure that creates a temporary table which is called from two different processes but from the same user/connection string, will that temporary table be shared between the two calls to that one stored procedure or will it be a case of each call to the stored procedure creates an unique temporary table instance.

I would assume that the temporary table belongs to the scope of the call to the stored procdure but i want to be sure before i go down a path with this.

Answer 1

Local temporary tables (start with #) are limited to your session; other sessions, even from the same user/connection string, can't see them. The rules for the lifetime depend on whether the local temporary table was created in a stored procedure:

• A local temporary table that is created in a stored procedure is dropped when the procedure ends; other stored procedures, or the calling process, can't see them.
• Other local temporary tables are dropped when the session ends.

Global temporary tables (start with ##) are shared between sessions. They are dropped when:

• The session that created them ends
• AND no other session is referring to them

This command can be handy to see which temporary tables exist:

select TABLE_NAME from tempdb.information_schema.tables 

And this is handy to drop temporary tables if you're not sure they exist:

if object_id('tempdb..#SoTest') is not null drop table #SoTest

See this MSDN article for more information.

Answer 2

The temporary table will be accesible to the instance of the procedure that creates it

The following script

Exec ('Select 1 as col Into #Temp Select * From #Temp')
Exec ('Select 2 as col Into #Temp Select * From #Temp')

Returns

Col
1

Col
2

Not

Col
1
2

Or an error because the table already exists.

The temporary table will also be accesible by any 'child' procedures that the initial procedure runs as well.

Answer 3

You might also think about using table variables. They have a very well-defined scope, and they are sometimes faster than their temporary table counterparts. The only problem with table variables is that they cannot be indexed, so some performance could be lost despite their nature. Check here for some more information on the subject.