2

I've got a report that will fire off a stored procedure on SQL Server. The report takes a couple minutes to run. Currently, in the development environment I'm writing to a couple of temp tables using this type of code:

SELECT *
INTO #Temp1
FROM MyTable
WHERE MyVal = 'X'

SELECT *
INTO #Temp2
FROM MyOtherTable
WHERE MyOtherVal = 'Y'

ETC...

Works fine in a dev environment, but there is a slight possibility that one day two people may kick this off at the same time with two different parameters. If they do, will that cause one #Temp to be overwritten? If so, what's the best way to handle that? Can you create a #Temp file name based on a timestamp or something?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
  • 11
    No, Temp tables created the way you're showing are session specific. Multiple users will not interfere with each other. – jradich1234 Mar 01 '16 at 14:28
  • 2
    If the temp table begins with #, then it's specific to that user. If you give it a ## at the start, then it'd be global to all users. – PulseLab Mar 01 '16 at 14:29
  • Thanks, guys! These are the pitfalls of being self-taught, but I'm always learning! – Johnny Bones Mar 01 '16 at 14:31
  • I was worried that connection pooling if you were using it, may cause some issues but it appears that's been considered: http://stackoverflow.com/questions/141718/sql-server-temp-tables-and-connection-pooling – xQbert Mar 01 '16 at 14:37
  • Possible duplicate of [Temp Table usuage in a Multi User Environment](http://stackoverflow.com/questions/4725812/temp-table-usuage-in-a-multi-user-environment) – Tab Alleman Mar 01 '16 at 14:45

1 Answers1

4

The first time I used temp tables, I wondered the same thing. Try this experiment in SQL server. In SSMS, run each of these queries and observe the results:

USE tempdb
GO

select * from sys.tables

SELECT * INTO #tablelist
FROM sys.tables

SELECT * INTO #tablelist2 
FROM sys.tables

-- The results of these two are different
SELECT * FROM #tablelist
SELECT * FROM #tablelist2

Look at the list of tables in your second query. You will notice two tables named like #tablelist_______ ... ____00007 and #tablelist______ ... ____00008.
This is how SQL server avoids name collisions for you. The table it actually creates has a name that is similar to your temp table name, but with a sequence number at the end, to avoid name collisions. It automatically handles this for you.

tgolisch
  • 6,549
  • 3
  • 24
  • 42