When exactly are multiple users unable to simultaneously run a stored procedure with a temp table?

I have a question regarding a piece of documentation on Temp Tables that I recently read on TechNet. The fourth paragraph of the Temporary Tables section on that page reads as follows:


If a temporary table is created with a named constraint and the temporary table is created within the scope of a user-defined transaction, only one user at a time can execute the statement that creates the temp table. For example, if a stored procedure creates a temporary table with a named primary key constraint, the stored procedure cannot be executed simultaneously by multiple users.

I work in an environment where we make significant use of a handful of stored procedures that use indexed temp tables, and we've never encountered an issue where users are having to wait for one execution to complete before the next begins. I hope that will continue to be the case, but I'm concerned that it could become an issue if this caveat is not properly understood.

Specifically, I am unclear on the following points:

• Does this apply only to global temp tables, or to local ones as

well? It seems strange that a table that isn't visible outside of
the session (as in the latter case) would prevent another session
from executing simultaneously.

• What qualifies as a "named constraint"? Don't all constraints have names (even if they are system-generated)? Is this referring to constraints with a user-defined alias? This seems like poor phrasing to me.

• Does "multiple users" actually mean multiple sessions? These procedures are called through our application using a single service account, so 99.9% of calls to our scripts are made to the DB by that single account (and I'm unconcerned about the occasional call an admin may make on the backend). If the service account can run the sproc in multiple sessions simultaneously, then this issue is moot for my purposes.

This applies to local temp tables.

The difference between named and unnamed constraints is this:

CREATE TABLE #t1 (c1 INT PRIMARY KEY CLUSTERED)

CREATE TABLE #t2 (c1 INT,
                     CONSTRAINT pk_c1 PRIMARY KEY  CLUSTERED(c1) )

Letting the system name constraints makes it extremely unlikely that there will be a collision. In this example, if you open two windows in SSMS, you'll be able
to create #t1 in both, but not #t2.

Global temporary tables are shared by all users, so you have to handle things differently. They don't get 'destroyed' until the last session is done using them, so you need to make sure that when users access them, they can only access their data. This is sometimes done by SPID, other times by a hash value. It depends on how the global temp table is used.

Typically for global temp tables, stored procedures will check to see if they exist, and then only create them if the OBJECT_ID() is NULL.

Multiple users means multiple sessions. The login name has nothing to do with it. If George runs sp_something @i = 1 and Gina runs sp_something @i = 2, it doesn't matter if they're both logged in as User1, they'll have different SPIDs.

StackExchange Database Administrators Q#174044, answer score: 11