Sql-server – SQL server temp table using # – is it only accessible by the containing query for multiple connections with the same login

ado.netconcurrencyconnectionssql servertemporary-tables

Say we have 2 completely different queries, that reference a temp table with the same name:

query one
….operates on:
#tempTableName

query two
….operates on:
#tempTableName

I've researched this and found out that "#temp is a local temporary table, and therefore not visible to other connections", however I'm not sure what a connection really means in-depth in the context of SQL queries. Does SQL consider there to be a separate 'connection' for every separate query that is run, or is a connection shared somehow?

Specifically, my question is "what's going on if the two queries in my scenario are being accessed by the same login" e.g. multiple .NET applications are using the same .NET connection string and concurrently accessing the database. Are they then going to potentially access the same temp table if both queries in my scenario are being run concurrently? What about locks? Do I need to do anything to prevent unwanted access during operations on the table?

I've looked at this answer, https://stackoverflow.com/questions/466947/are-temporary-tables-thread-safe, however I really need something like a 'pretend I'm 6 yrs old' explanation so that I'm crystal clear on what's going on in my hypothetical scenario in terms of exactly how the SQL queries are utilising connections and determining exclusive access etc. Any diagrams or pointers to resources that can give me a complete command of this would be great!

Best Answer

Local temporary objects are separated by Session. If you have two queries running concurrently, then they are clearly two completely separate sessions and you have nothing to worry about. The Login doesn't matter. And if you are using Connection Pooling that also won't matter. Local temporary objects (Tables most often, but also Stored Procedures) are safe from being seen by other sessions.

While your code has a single, common name for the local temporary objects, SQL Server appends a unique string to each object name per each session (and if needed, per each sub-process) to keep them separate. You can see this by running the following queries in SSMS:

CREATE TABLE #T (Col1 INT)

SELECT * FROM tempdb.sys.tables WHERE [name] LIKE N'#T%';

You will see something like the following for the name (I removed most of the underscores from the middle of the name to prevent the need to scroll here):

#T_______________00000000001F

Then, without closing that query tab, open up a new query tab and paste in those same 2 queries and execute them. You should now see something like the following:

#T_______________00000000001F
#T_______________000000000020

So, each time your code references #T, SQL Server will translate it to the proper name based on the session. It is all handled auto-magically :-).

To illustrate this point. I have worked on systems that are highly transactional (thousands of transactions per second) and were SaaS (Software as a Service) web apps running 24 / 7. All T-SQL code was in Stored Procedures (meaning: same local temp table name for every execution of that code) and we made pretty good use of local temporary tables. Being a web app, the Login was the same across nearly all connections and we definitely used connection pooling. We never had any problems indicating any cross-Session access of same-named local temporary objects. And quite frankly, we would have been shocked and making use of our support contract with Microsoft to get it fixed if that ever did happen.

Additional points to keep in mind regarding local temporary tables:

  1. While their names are unique, their dependent objects are not. You cannot create Triggers or Foreign Keys on temporary tables, so this is really about Primary Keys, Check Constraints, and Default Constraints. This means that naming a Primary Key #PK_#T does not make it a unique name with a behind-the-scenes unique id appended to the name. If you attempt this, you will get "Cannot create object. Object already exists." errors (well, assuming multiple concurrent executions this same code). So, if you need any of these 3 object types for a local temporary table, create the dependent object(s) inline so it/they will get system-generated names which will be unique and not conflict between Sessions.

    You don't need to worry about Index names since those are already separated per [object_id].

  2. Given that you get "Cannot create object. Object already exists." errors when creating named dependent objects, that brings up a good point that is most often overlooked regarding this question of "are local temporary objects completely isolated per session": If local temporary tables could be seen by other Sessions, then the CREATE TABLE statement would get an error, right?

  3. A nuance of local temporary tables (that is also a loose counter-point to #2 above) is that if you reference a local temporary table in a sub-process (i.e. EXEC) that was created prior to that sub-process starting, it will be able to see (and even modify) that local temporary table. HOWEVER, if that sub-process creates another temporary table having the same name, then there will be two objects, with separate unique IDs appended to the end, that are accessible via the short name. I recall reading somewhere that there is no guarantee that referencing #name in the sub-process will always resolve to the version of that object that was created in the sub-process. Hence, it is best to have some uniqueness of local temp table names between Stored Procedures that have any possibility of being executed in a chain of nested Stored Procedure calls.

Related Question