patternsqlMajor

Is it possible for SQL statements to execute concurrently within a single session in SQL Server?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

sqlstatementsconcurrentlywithinpossiblesingleforsessionserverexecute

Problem

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.

Solution

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 contrived 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 or batch ends.

Code Snippets

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();
        }
    }
}

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

create table #t(id int)

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

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

Context

StackExchange Database Administrators Q#235197, answer score: 20

Revisions (0)

No revisions yet.