ASynchronous Creation of Temp Tables in SQL Server

I’m currently trying to determine possible issues with creating temporary tables from a web application and how SQL Server naturally determines separate sessions.

SELECT blabla, lala FROM table INTO #TempTable
SELECT blabla FROM #TempTable
DROP TABLE #TempTable

In the above, while one user of a web application is waiting for the second line to execute, and another user fires off the same 3 lines, what would determine whether the 2nd user gets a “Object already exists” or else a new #TempTable is created for that user.

If each user was on a separate computer on the same network, would SQL server treat this as separate sessions and thus create separate temporary tables.

What about if it is run on the same computer on two different networks?

Answer

Each user connection to the database is it own session. These sessions are unique even if you’re using connection pooling within SQL Server. Behind the scenes, SQL Server appends each #tempTable with a single session reference number, so they technically aren’t even named the same thing during execution.

If the root of your problem is an error message about the object already existing when you are debugging. Try adding the code snippet below before you create the temp table:

IF OBJECT_ID('[tempdb]..[#tempTable]') IS NOT NULL
BEGIN
    DROP TABLE #tempTable
END