February 19, 2009 at 6:53 pm
Say, I create a temporary table using the following script.
CREATE TABLE #temp(col1 INT)
Now, looking at SSMS, I can see the name of this temporary table has a long attached characters at its end.
Does anyone know how to interprete the attached characters
Any input will greatly be appreciated.
February 19, 2009 at 9:58 pm
SQL ORACLE (2/19/2009)
Now, looking at SSMS, I can see the name of this temporary table has a long attached characters at its end.Does anyone know how to interprete the attached characters
It's just a hash to ensure the table name is unique. In 2000 it used to be possible to get the creating SPID (I think), but not in 2005.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 20, 2009 at 9:08 am
Gail:
Both SQL 2000/2005 have the same issue. I know the attached characters are related to the connection session. If we open two connection sessions, and run the following script on each one,
CREATE TABLE #tempA (col1 INT)
we can see from GUI that we have two temporary tables with the name comining tempA and different attached strings.
If we run the following scripts from one connection session,
CREATE TABLE #tempA (col1 INT)
CREATE TABLE #tempB (col1 INT)
we can see two temporary tables with the names tempA and tempB, attached by the same string.
I know the string is related to a connection session, but I do not know how to interprete it.
Many thanks for your input.
February 20, 2009 at 9:22 am
It's just an incrementing hex number.
I just created three temp tables from one connection and four from another. Then I selected the last 12 characters of each temp table's name from tempdb.sys.tables. The results were:
00000000060D
00000000060E
00000000060F
000000000610
00000000060B
00000000060A
00000000060C
The first one created was 60A, the second one was 60B, then 60C, then D, E, F, followed by 10. It's incrementing hex.
@@version =
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Judging by that, I'm going to say there isn't a connection between that and the SPID.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 20, 2009 at 10:00 am
It's just an incrementing hex number.
Thank you so much for your conclusion.
February 20, 2009 at 12:32 pm
GSquared (2/20/2009)
Judging by that, I'm going to say there isn't a connection between that and the SPID.
I've seen docs that say there was on SQL 2000, but not on 2005 or any higher version.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 20, 2009 at 1:31 pm
Thanks all.
In SQL 2000 and Sybase 12.5, the attached string is related to connection session. In SQL 2005, it is not.
🙂
February 20, 2009 at 1:37 pm
Just did the same test in SQL 2000, and the temp tables from one connection ended with one number, while the tables from the other connection had a different number. In each case, the number was the same for all the tables from each connection.
Checked @@spid, and it didn't seem to have any bearing on the number, so I'm not sure what it's significance is, if anything, but it was consistent.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply