HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMajor

Can two sessions create #temp tables with the same name?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
cantablesthesamecreatewithtemptwonamesessions

Problem

I am creating a temporary table (#myTable) and using a cursor. Does this create a problem when concurrent users are accessing the cursor through my application? Does it allow me to create separate temp tables with the same name?

Following is the sample code:

Open cursor;
Fetch Next from cursor into @Variable_Temp_Table_Name;
Create table #myTable(pk int)
While @@Fetch_Status = 0
Begin    
Fetch Next from cursor into @Variable_Temp_Table_Name;
End

Solution

SQL server always append some random number in the end of a temp table name (behind the scenes), when the concurrent users create temp tables in their sessions with the same name, sql server will create multiple temp tables in the tempdb.

I created 3 temp tables called #TempTable in three different sessions in my SSMS, now if I go to the tempdb I can see the temp tables created there with a random (unique) string appended to each temp table's name.

Context

StackExchange Database Administrators Q#116240, answer score: 29

Revisions (0)

No revisions yet.