Arsthbt

I have written a stored procedure which makes use of a temporary table. I know that in SQL Server, temporary tables are session-scoped. However, I have not been able to find definitive information on exactly what a session is capable of. In particular, if it is possible for this stored procedure to execute twice concurrently in a single session, a significantly higher isolation level is required for a transaction within that procedure due to the two executions now sharing a temporary table.

Not concurrently. Your options include:

• Run the queries one after another in the same session


• Switch from a temp table to a global temp table (use ##TableName instead of #TableName), but be aware that the global temp table is automatically dropped when the session that created the temp table closes, and there are no other active sessions with a reference to it


• Switch to a real user table in TempDB - you can create tables there, but be aware that they'll disappear on server restart


• Switch to a real user table in a user database

While Brent's answer is correct for for all practical purposes, and this is not something I've ever seen someone worry about, it is possible for multiple invocations of a stored procedure in a session to affect each other through a session-scoped #temp table.

The good news is it's extremely unlikely to happen in the wild because

1) #Temp tables declared inside a stored procedures or nested batches don't actually have session visibility (or lifetime). And these are by far the most common case.

2) It requires MultipleActiveResultsets and either some very strange async client programming, or for the stored procedure to return a resultset in the middle, and the client to call another instance of the stored procedure while processing the results from the first.

Here's a cooked-up example:

using System;
using System.Data.SqlClient;

namespace ado.nettest

 class Program
 
 static void Main(string[] args)
 
 using (var con = new SqlConnection("Server=localhost;database=tempdb;integrated security=true;MultipleActiveResultSets = True"))
 
 con.Open();

 var procDdl = @"
create table #t(id int)
exec ('
create procedure #foo
as
begin
 insert into #t(id) values (1);
 select top 10000 * from sys.messages m, sys.messages m2;
 select count(*) rc from #t;
 delete from #t;
end
');
";
 var cmdDDL = con.CreateCommand();
 cmdDDL.CommandText = procDdl;
 cmdDDL.ExecuteNonQuery();

 var cmd = con.CreateCommand();
 cmd.CommandText = "exec #foo";
 using (var rdr = cmd.ExecuteReader())
 
 rdr.Read();

 var cmd2 = con.CreateCommand();
 cmd2.CommandText = "exec #foo";
 using (var rdr2 = cmd2.ExecuteReader())
 

 

 while (rdr.Read())
 

 
 rdr.NextResult();
 rdr.Read();
 var rc = rdr.GetInt32(0);
 Console.WriteLine($"Numer of rows in temp table rc");

 


 

 Console.WriteLine("Hit any key to exit");
 Console.ReadKey();
 
 

which outputs

Numer of rows in temp table 0
Hit any key to exit

because the second invocation of the stored procedure inserted a row, and then deleted all the rows from #t while the first invocation was waiting for the client to fetch the rows from its first resultset. Note that if the first resultset was small, the rows might get buffered and execution could continue without sending anything to the client.

If you move the

create table #t(id int)

into the stored procedure it outputs:

Numer of rows in temp table 1
Hit any key to exit

And with the temp table declared inside the procedure, if you change the second query to

cmd2.CommandText = "select * from #t";

It fails with:

'Invalid object name '#t'.'

Because a #temp table created inside a stored procedure or nested batch is only visible in that stored procedure or batch and in nested procedures and batches that it calls, and is destroyed when the procedure ends.