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):
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:
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:
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
- 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?
- 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.