Have you ever noticed negative object_ID values when working with SQL Server ‘Denali’? If so, you’re not alone. In this article, we will explore the concept of negative object_IDs and discuss their implications.

Let’s start by running a simple script that generates random large results:

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

When running this script on SQL Server ‘Denali’, you may notice that it returns negative object_ID values. This behavior is quite different from previous versions of SQL Server, where object_IDs were always positive.

Curious about this behavior, I decided to investigate further. I discovered that the negative object_IDs are associated with objects belonging to TempTables. To verify this, I ran the following command:

CREATE TABLE #LocalTempTable (ID INT)

After executing this command, I found that a negative ID was added to the objects table. To confirm my findings, I ran the following command:

SELECT * FROM tempdb.sys.tables

Indeed, every time a LocalTempTable is created in SQL Server ‘Denali’, it is assigned a negative object_ID. However, in previous versions of SQL Server, the object_IDs for LocalTempTables were always positive.

On the other hand, when creating Global TempTables using the following code:

CREATE TABLE ##GlobalTempTable (ID INT)

The resulting object_IDs for global TempTables are always positive.

Now, let’s address a few common questions:

Are there any other objects in SQL Server ‘Denali’ that have negative object_IDs?

No, negative object_IDs are only associated with LocalTempTables in SQL Server ‘Denali’.

What can be the reason behind the negative object_IDs?

The reason behind the negative object_IDs for LocalTempTables in SQL Server ‘Denali’ is to differentiate them from other objects and provide a clear distinction.

Finally, if you have updated to SQL Server ‘Denali’, it’s important to update your code that checks for the existence of a LocalTempTable. Instead of using:

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

Use either:

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

or

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

This ensures compatibility with SQL Server ‘Denali’ and future versions.

In conclusion, understanding the concept of negative object_IDs in SQL Server ‘Denali’ is crucial when working with LocalTempTables. By differentiating these objects with negative IDs, SQL Server provides a clear distinction and avoids any potential conflicts.