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.

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.

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
orIF OBJECT_ID('tempdb.dbo.#LocalTempTable') IS NOT NULL

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.

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.