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.

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

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.

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

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 (https://blog.sqlauthority.com)

,
Previous Post
SQLAuthority News – Solid Quality Journal – Importance of Statistics
Next Post
SQL SERVER – Tomorrow 2 Sessions on Performance Tuning at TechEd India 2011 – March 25, 2011

Related Posts

6 Comments. Leave new

  • I hope you will answer the second question i.e., reason behind having the object ids with negative sign.

    -P

    Reply
  • I have SQL Server 2008 and 2008R2 and I can see -ve object ids on both of them. Any idea??

    Reply
  • I have SQL Server 2005 and there I can also see negative object ids.

    Reply
  • Nakul Vachhrajani
    April 22, 2011 4:37 pm

    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.

    Reply
  • Polly Pran Bora
    June 9, 2011 12:21 pm

    I can also see a lot of objectIDs with -ve value in SQL server 2008 R2.

    Reply
  • CASSIO MURAKAMI
    November 27, 2019 6:22 pm

    Hi Pinal, I’m testing the SNAPSHOT isolation level, and looking to the temporary tables, I saw 2 or 3 temporary tables named like “#AC66249C”. However, I can’t use SP_SPACEUSED or even a SELECT * FROM with these temporary tables.
    How can I check if those temporary tables are really the tables used by SNAPSHOT? And how can I check the size os those tables?

    Reply

Leave a Reply

Menu