sql-serversql-server-2005

SQL Server Temp Tables and Connection Pooling


I have a multi-user ASP.NET app running against SQL Server and want to have StoredProcA create a #temptable temp table - not a table variable - to insert some data, then branch to StoredProcB, StoredProcC, and StoredProcD to manipulate the data in #temptable per business rules.

The web app uses connection pooling when talking to SQL. Will I get a new #temptable scratch area for each call of StoredProcA? Or will the connection pooling share the #temptable between users?


Solution

  • Connection pooling (with any modern version of SQL Server) will call sp_reset_connection when reusing a connection. This stored proc, among other things, drops any temporary tables that the connection owns.