Thursday, June 28, 2007

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

   [id] [int] NOT NULL,
   [id2] [int] NOT NULL,
   [foo] [decimal](6,3) NOT NULL,

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

   [id] [int] NOT NULL,
   [id2] [int] NOT NULL,
   [foo] [decimal](6,3) NOT NULL,

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


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.


Confused in Yooperland.


Delphi / SQL Developer said...

No, but I could have written the same post you did. My experience exactly. Same work around also. I think this may be the case with 2000 as well as 2005.

Even though it's a temporary table, I'm guessing you name the primary key as a convention - you do it for normal tables so that you can have easy programatic control over then at some stage if necessary. Probably unnecessary for temporary tables, but nice to keep the option open. That's my reason anyway...

Jason Poll said...

That is exactly why I was naming the primary key of my temp table: strictly out of habit.

Or, at least, it hadn't even occurred to me at the time that I didn't have to give the primary key a name. :)