In MS SQL Server 2005, what happens when different executions of the same SP access the temporary table?

Question

I have a stored procedure that first checks the temporary table (#temp_table), deletes it if it exists, uses the table, and then finally deletes it. This SP is called randomly when the user does something to invoke it, and it is likely that sometimes the SP will run simultaneously - or with some overlap.

Suppose I have Exec1 and Exec2, both from the same SP create, modify, and drop the #temp table, and they work in milliseconds from each other.

What's happening? Will Exec1 block #temp_table while Exec2 waits for Exec1 to finish? This would obviously be desirable in my case. I would not want both Exec1 and 2 to use the table in Sametime, and I would not want Exec2 to fail because Exec1 already uses the table.

[EDIT] Should I just convert my temporary table to a table variable?

Answer 1

In a sql server, if you create a local tempo table, with a single sql-sign # server, several sub-points and some identifier at the end are used. Suppose you created a Temp table named #Temp sql server in temp db Creates a table named #Temp_______10912098 , each Temp table created in separate connections will have its own identifier at the end of the name.

These are all temporary tables created in different connections, all have the name #Temp , but are added with some underscores, and the unique id sql server uses to distinguish them.

Answer 2

The scope of temp #table limited by your session, so this should not be a problem.

If you used the ##table , then this is global and you will have problems.

See here: MSDN SQL Tables

In particular, this bit:

If the database session creates the local temporary table #employees , only the session can work with the table, and it is deleted when it is disconnected. If you create a global temporary table ##employees , any user in the database can work with this table. If no other user works with this table after its creation, the table is deleted when it is disabled. If another user is working with the table after its creation, SQL Server deletes it after disconnecting and after all other sessions will no longer actively use it.

Answer 3

Temp tables named hash # refer to a single join.

Therefore, if two joins (also called “processes” or “SPIDs”) refer to a temporary table with the same #tablename , they will actually refer to different tables.

You can see this if you look in tempdb. There will be several tables with names such as #748B826C . These are actually temporary table variables, such as declare @t table (ix int identity primary key) and temp tables name with a hash.

Thus, provided that these are different connections and not recursive triggers, there should be no problem.

however, if you are concerned about the possibility of recursive triggers, you should use table variables instead. They are limited to the scope of a batch or stored procedure.