Are #temptables unique?

Question

Are #temptables unique?

Christopher Klein

SSCrazy

Points: 2648
More actions
October 12, 2007 at 6:59 am

#178379

Just a pre-coffee question but are tables defined as #temp unique to a particular instance of being called?
Say I have a stored procedure that builds up a #temp table, do I have to worry that two different users running the same proc will trip over one another?
I had thought that when you created a #temp table that it actually has a unique internal name but I can't find anything on it in particular.
thanks,
Chris

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 1

The scope of the temp table is local to the stored procedure and it will not create any multi-user conflicts if the same procedure is accessed by mutlitple users at the same time. However it is a good standard to use drop temp tables after you have completed using them inside to procedure even though the scope of the temp table would end after the execution of procedure is completed.

Prasad Bhogadi
www.inforaise.com

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 2

local temptables have the scope of a connection, the name SQL Server creates for them in tempdb is postfixed with undescores and some number.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Lowell SSC Guru Points: 323518 More actions · Answer 3

to add to what Prasad said, you can see the results for yourself. create a temp table, and peak at tempdb: a table unique to your session gets created...it may start with the spelling you made, but a LOT of undersores, and then a uniqueid is appended to teh end of it.

that way, if the a different user creates a temp table with the same name, it's still unique.

you can make a "global" temp table with double pound signs, Create table ##tmp, and there will only be one, which can be accessed accross sessions.

create table #tmp(id int)

select * from tempdb.dbo.sysobjects where name like '#tmp%'

#tmp__[108 more underscores]__00000000116F

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jez-448386 SSCarpal Tunnel Points: 4596 More actions · Answer 4

If you are concerned about whether #tables are unique (and I think that they are), then use table variables instead.

There are a few differences such as you have to declare the table variable explicitly rather than creating it with a select into and you cannot index table variables (other people will point out other differences I'm sure).

Just an idea.

Jez

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 5

As one of those other people who will point out some of the differences, I suggest you have a look at http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

Under 2005 the performance of table variables for large tables is worth keeping a close eye on.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Jez-448386 SSCarpal Tunnel Points: 4596 More actions · Answer 6

Andras,

Are you suggesting that #temp tables are preferable to table variables?

Jez

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 7

Both of them have their places. But do not store much data in table variables. Not only they suffer from the above performance problems, you cannot create non-clustered indexes on them, no statistics, etc. They are however created in the tempdb the same way as temp tables.

Have a look at http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Jez-448386 SSCarpal Tunnel Points: 4596 More actions · Answer 8

Thanks.

I hadn't realised that it was possible to create constraints (PK, unique) on table variables so that's something else I learnt today.

Jez

Christopher Klein SSCrazy Points: 2648 More actions · Answer 9

Christopher Klein

SSCrazy

Points: 2648

October 12, 2007 at 9:34 am

#741646

Thanks for the replies!

Jeff Moden SSC Guru Points: 1004504 More actions · Answer 10

Christopher Klein (10/12/2007)
Just a pre-coffee question but are tables defined as #temp unique to a particular instance of being called?
Say I have a stored procedure that builds up a #temp table, do I have to worry that two different users running the same proc will trip over one another?
I had thought that when you created a #temp table that it actually has a unique internal name but I can't find anything on it in particular.
thanks,
Chris

I know folks have already answered, but thought I'd throw my 2 cents in, anyway...

Yes, temp tables are unique between sessions.

No, you do not have to worrty that two different users are running the same proc.

All bets are off if you use a "Global" temp table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)