Weird SQL2005 TempDB Table and Primary Key Behavior

Ok, here's something I ran into the other day while working with some temp tables. I needed a temp table. That table was probably going to be bloated, and could benefit from a primary key and some extra indexes on it since I'd be doing some heavy queries against the table. The SQL used in this post isn't the same SQL as from my app, rather, it's been simplified to the point of demonstrating my problem.

Consider the create-table statement below:

Figure 1

CREATE TABLE #myTmp (
   [id] [int] NOT NULL,
   [id2] [int] NOT NULL,
   [foo] [decimal](6,3) NOT NULL,
   CONSTRAINT [PK_myTmp] PRIMARY KEY CLUSTERED ([id] ASC, [id2] ASC))

Ok, so this gives us a very simple temp table that's scoped to the connection (or stored procedure) that it's create inside. It also has a primary key.

I'd been designing this temp table, and testing my SQL in a query window inside Management Studio. I had run this SQL, and the query window and it's connection were still open when I copy-n-pasted the code into my application and took it for a test run.

I got an error:
"There is already an object named 'PK_TmpWorking' in the database."

Whaaaaaat? It's as though the name of the primary key doesn't follow the same scoping rules as other objects created in TempDB! This seems really weird to me, and my google-spelunking didn't turn up many answers (am I loosing my touch?) The only way around this was to not name the primary key constraint, like so:

Figure 2

CREATE TABLE #myTmp (
   [id] [int] NOT NULL,
   [id2] [int] NOT NULL,
   [foo] [decimal](6,3) NOT NULL,
   PRIMARY KEY CLUSTERED ([id] ASC, [id2] ASC))

This leaves SQL Server to come up with a name for the primary key, and it appears that it names it some random jibber-jabber.

So, while I can't really logic-out an answer as to why I'm seeing this behavior, I do at least have a work-around. But get this, as if this primary-key thing isn't weird enough, it would seem this 'bug' only exists for primary keys, and not other named indexes. What I haven't shown you is that, in my query window, I also had a number of CREATE INDEX statements, similar to:

Figure 3

CREATE NONCLUSTERED INDEX [IX_MyTmp_Foo] ON #myTmp ([foo] ASC)

I had this executed in my query window. The temp table had it's randomly-named primary key, and it had this named index. When I ran the same code in my application, there was no complaints at all. I would have expected to run into the same "There is already an object named 'IX_MyTmp_Foo' in the database." error, but I didn't get one!


Does any of this make sense? Can anyone explain why it appears that primary keys on temp tables aren't scoped within the same scope as the temp table itself? Especially since regular indexes appear to be scoped as you would assume.

Signed:

Confused in Yooperland.

Oh wowwwww...

Ok, in this day in age, it's difficult to make me so awe-struck that my jaw hits the floor. Well, I finally got around to checking out the Tech Preview of Microsoft Live Labs' Photosynth, and let me tell you, my jaw is still on the floor. Totally awesome technology.

I'm going to give you guys two links, one that will take you to a MS Live Labs blog post containing a video of a guy at TED (Technology, Entertainment, Design) Conference in Monterey, California explaining a couple of new MS-acquired technologies. Photosynth is included. Find that post here.

Then, go and install and try Photosynth yourself. You will need to install the browser plugin to use it.