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.
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
or
IF OBJECT_ID('tempdb.dbo.#LocalTempTable') IS NOT NULL
Reference: Pinal Dave (https://blog.sqlauthority.com)
