CODEWITHSUNDEEP
sqlsql-serversql-server-2005sql-server-2008
newbie
newbie

Reputation: 1505

Temp Table unique across multiple requests on the same connection pool?

I have the following stored proc which uses a temp table to bulk import the data. I understand the temp tables are unique for every session, however i'm wondering if my application uses threads and makes multiple concurrent request to the stored proc, using the same sql connection from the application pool, will they end up referencing the same temp table?

CREATE PROCEDURE [dbo].[Mytestproc]
AS
  BEGIN
      BEGIN TRANSACTION

      CREATE TABLE #Hold
        (
           ID INT,
           VAL NVARCHAR(255)
        )

      BULK INSERT #Hold
        FROM 'C:\data.txt'
        WITH
          (
            FieldTermInAtOr ='|',
            RowTermInAtOr ='\n'
          )

      SELECT *
      FROM   #Hold

      DROP TABLE #Hold

      COMMIT TRANSACTION
  END

Upvotes: 4

Views: 2077

Answers (2)

user596075
user596075

Reputation:

I would think that if your application is making concurrent calls to this stored procedure, it would be separate connections, in which case they would be separate temp tables.

Best way to find out is to test it, though. Have your application make concurrent calls, and to hold these connections. Then execute sp_who to see if connections are multiple for your application, and view the output of these temp tables (given that they contain different data).

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

Whilst one thread is using a connection and executing this stored procedure, that same connection cannot be reused by the connection pool - so there's no danger of sharing there. Other threads cannot use this connection, and will open new ones instead.

In addition, there's no need to drop the temp table before the stored procedure ends - temp tables created within a stored proc are dropped automatically when the proc is exited.

Upvotes: 3

Related Questions