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.