25

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
Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614
sujith karivelil
  • 567
  • 1
  • 6
  • 14

2 Answers2

29

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.

enter image description here

M.Ali
  • 1,960
  • 9
  • 27
  • 38
15

Yes, multiple applications will get their own copies of the #temp table. That is the point of using a #temp table, because each concurrent session has their own, isolated object. This has nothing to do with whether you are using a cursor in combination with your #temp table (though I suspect the cursor isn't needed anyway - you didn't include enough code to comment specifically).

Edit to include comment:

One additional thing about the using #temp tables is that if you need to add constraints to them let SQL server generate the name otherwise even though the table will be unique to the session the constraint won't and the second instance will error out creating the table.

Aaron
  • 1,771
  • 1
  • 11
  • 23
Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614
  • 7
    One additional thing about the using #temp tables is that if you need to add constraints to them let SQL server generate the name otherwise even though the table will be unique to the session the constraint won't and the second instance will error out creating the table. – Aaron Sep 26 '15 at 15:27
  • 1
    @Aaron - Suggest that you move the comment on unnamed constraints into the answer. Lots of people mess up on that detail. – RLF Sep 26 '15 at 16:40
  • Also note that if someone does want a global temp table, they can declare it ##likeThis. – underscore_d Sep 26 '15 at 20:54
  • @RLF and Aaron: one can always generate a GUID with NEWID() and create the constraint via Dynamic SQL. Not saying that is as clean as including it in the CREATE TABLE statement, but it is at least an option. And I also believe that FK constraints are not allowed on temp tables (as long as we are speaking of constraints generically). – Solomon Rutzky Sep 26 '15 at 21:15
  • @srutzky - True, but named constraints are usually named so as to make it easy to reference them from the code. Using a NEWID() will not provide that simplification, though you can query to find the NEWID if needed. – RLF Sep 28 '15 at 17:29
  • @RLF That is a moot point in this context since: a) letting SQL Server name the constraint gives you no insight without querying for it, and b) you can create a variable, store the GUID in there, use it to create the constraint, and then have it to use again to reference the constraint in the code that follows. :-) – Solomon Rutzky Sep 28 '15 at 17:35