SQL SERVER – Denali – ObjectID in Negative – Local TempTable has Negative ObjectID

I used to run the following script to generate random large results. However, when I ran this on Denali I noticed a very interesting behavior:

SELECT o1.OBJECT_ID,o1.name, o2.OBJECT_ID, o2.name
FROM sys.all_objects o1
CROSS JOIN sys.all_objects o2

I noticed lots of negative object_ID’s on Denali, whereas my experience on SQL Server 2008 R2 as well as the earlier versions was it was always giving me a positive number.

TempNegative SQL SERVER   Denali   ObjectID in Negative   Local TempTable has Negative ObjectID

This whole thing interested me so I decided to find out objects which belonged to the negative object_ID. When I looked at the name of the object, it was very evident that it belonged to TempTable.

TempNegative1 SQL SERVER   Denali   ObjectID in Negative   Local TempTable has Negative ObjectID

To verify my finding, I ran the following command for a couple of times:

CREATE TABLE #LocalTempTable (ID INT)

Then, I realized that every time I ran this command, I found one more negative ID added in the objects table. To further clarify this, I ran the following command:

SELECT *
FROM tempdb.sys.tables

This command really validated the event that whenever LocalTempTable is created in the Denali, it creates object_ID with negative number. However, in SQL Server 2008 R2 and earlier versions, it is always a positive number.

I attempted to create Global TempTable using the following code. The result was all of the global TempTable created Object_ID’s which yield positive numbers. For the moment, it looks like all the LocalTempTable have negative ID’s.

CREATE TABLE ##GlobalTempTable (ID INT)

Let me ask you a few questions:

  • Are there any other objects in SQL Server ‘Denali’ that have negative object_ID?
  • What can be the reason behind  the negative object_ID?

On a separate note, take a look at the following code:

IF OBJECT_ID('tempdb.dbo.#LocalTempTable') > 0

If you updated to Denali you should change it to either
IF
OBJECT_ID('tempdb.dbo.#LocalTempTable') <>0

or
IF OBJECT_ID('tempdb.dbo.#LocalTempTable') IS NOT NULL

Reference: Pinal Dave (http://blog.SQLAuthority.com)

7 thoughts on “SQL SERVER – Denali – ObjectID in Negative – Local TempTable has Negative ObjectID

  1. I have not tested this (don’t have a SQL Server instance nearby right now :( ), but I believe negative object IDs might be used by the SQL Server for containment. Temporary objects created within contained databases (when fully contained – full containment is not available CTP01), may need a different object id series. It’s just a guess, but thought of putting it on the table.

    Like

  2. Pingback: SQL SERVER – Debate – Table Variables vs Temporary Tables – Quiz – Puzzle – 13 of 31 « SQL Server Journey with SQL Authority

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #021 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s