Temporary tables in stored procedures

Question

I have been wondering about temp tables in sp's and how all that can effect concurrency. SP made on a MSSQL 08 server.

If I have a SP where I create a temp table and drop it again like this:

BEGIN

CREATE TABLE #MyTempTable
(
   someField int,
   someFieldMore nvarchar(50)
)

... Use of temp table here
... And then..

DROP TABLE #MyTempTable

END

This SP will be called very very often, so my question is can there ever occur concurrency issues here?

Answer 1

Nope. Independent instances of the temporary table will be created per each connection.

Answer 2

Maybe.

Temporary tables prefixed with one # (#example) are kept on a per session basis. So if your code calls the stored procedure again while another call is running (for example background threads) then the create call will fail because it's already there.

If you're really worried use a table variable instead

DECLARE @MyTempTable TABLE 
(
   someField int,
   someFieldMore nvarchar(50)
)

This will be specific to the "instance" of that stored procedure call.

Answer 3

Not really and I am talking about SQL Server. The temp table (with single #) exists and is visible within the scope it is created (scope-bound). Each time you call your stored procedure it creates a new scope and therefore that temp table exists only in that scope. I believe the temp tables are also visible to stored procedures and udfs that're called within that scope as well. If you however use double pound (##) then they become global within your session and therefore visible to other executing processes as part of the session that the temp table is created in and you will have to think if the possibility of temp table being accessed concurrently is desirable or not.

Answer 4

The database uses the same lock for all #temp tables so if you are using a lot you will get deadlock problems. It is better to use @ table variables for concurrency.

Answer 5

Use @temp tables whenever possible--that is, you only need one primary key and you do not need to access the data from a subordinate stored proc.

Use #temp tables if you need to access the data from a subordinate stored proc (it is an evil global variable to the stored proc call chain) and you have no other clean way to pass the data between stored procs. Also use it if you need a secondary index (although, really ask yourself if it is a #temp table if you need more than one index)

If you do this, always declare your #temp table at the top of the function. SQL will force a recompile of your stored proc when it sees the create table statement....so if you have the #temp table declaration in the middle of the stored proc, you stored proc must stop processing and recompile.