CODEWITHSUNDEEP
sqlsql-servert-sql
Law
Law

Reputation: 129

Are temp table indexes unique across sessions or are they shared?

i have a large query (Web Dashboard Query) with many temporary tables.i have created indexes on the temp table.the application that is using the query has a user management module with different levels of permissions.My question is are indexes created per session like the temp db ? i don't want the indexes to be shared across sessions.

i have been doing something like

EXEC('CREATE INDEX idx_test'+ @sessionId + 'ON #TempTable (id1,id2)');

is this necessary. i have seen it done by some developers.

Upvotes: 0

Views: 750

Answers (1)

sepupic
sepupic

Reputation: 8687

Indexes on temporary tables (#t, not ##t) are not shared across the sessions, and there is no need to invent a unique index name to an index on a temporary table.

What is different (and may be you have seen in the code from other developers) is CONSTRAINT NAME. Index name can be repeated many times for different tables, but a constraint name must be unique within the database.

So maybe you see the code for stored procedures that create a constraint name with reference to a session, this is an attempt to give a unique name to a constraint. Because if you launch a stored procedure that creates a temp table #t in two sessions, every session create it's own table with it's own name(not just #t, the system is adding additional symbols to a table name that makes it unique)

But if the same proc tries to create a CONSTRAINT PK_t, the first session will succeeded but the second will get an error that the constraint PK_t already exists in the database(tempdb)

Upvotes: 2

Related Questions