Temporary tables in SQL Server

Question

Temporary tables in SQL Server

Ravi SQL

SSCommitted

Points: 1714
More actions
November 23, 2004 at 11:16 am

#163930

Hi,
My question is, in SQL Server when I create a temporary table, in tempdb database the table name is shown along with underscores and system generated code. What does this mean n why do the system generates the name like that ?
Thanks,
Ravi.
Regards,
Ravi.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

I'm not sure how SQL is coming up with the generated code, but it is possible that SQL is using this method to uniquely identify the table. For example, if create a session using Query Analyzer and create a temp table called #tempTable and then open another session and create another #tempTable, these are two separate objects that actually have distinct names. I own both tables but they are part of two separate sessions.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

To a certain extend you can read about it on BOL at CREATE TABLE under the Temporary Table section.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Sam Shiell Ten Centuries Points: 1373 More actions · Answer 3

What if 2 or more users co-incidentally create a temp table called #Temp1 at the same time? As these are all held in tempDB the sysobjects tables needs to make up a unique name for each and that's why you see all of the underscores and other stuff.

But why are you looking there? Is it just out of interest or is this part of your processing? If so then I'd try to steer clear of it and think of another way because you can't be sure you're seeing your own table with that name.

Sam

Ravi SQL SSCommitted Points: 1714 More actions · Answer 4

Hi Sam,

I would like to know what the code indicates, which is at the end of the table name...

Thanks,

Ravi.

Regards,
Ravi.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Why? It isn't important after all, since you can't adress it anyway.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Peter Wright SSCommitted Points: 1966 More actions · Answer 6

The system generated code doesn't 'mean' anything, it's just the method SQL Server uses to distinguish this session's temporary table from another session's temporary table. The source code is owned by Microsoft, they may publish it but I doubt it very much.