SQL Server assigns each object (tables, view, stored procedure, functions etc) a unique ID and that ID we call as an Object_ID. In simple words, we can say that it creates uniqueness between two objects.
In this article, we will learn how it works, what is the next counter, etc.

What will be the first object_ID in the new database ?
If you create a new object in the database, the first ID will always be 2073058421 in SQL SERVER 2005 and 245575913 in SQL SERVER 2012.
The difference between both versions is due to some new internal tables introduced in SQL SERVER 2012 and got the new ID 2073058421 (filestream_tombstone_2073058421) once you create a database in 2012.

How to get this Object_ID from SQL ?
You just need to use a function OBJECT_ID, to retrieve object ID of any object. For example :

Select Object_ID('test_table');

What will be the object_ID increment counter for user defined objects ?
It will add 16000057 + Last user defined object_ID and will give you a new ID.

What will be the object_ID increment counter for system objects ?
It varies from 1 onwards.

If we drop an object and create the same object, does SQL assign the same object_ID ?
No, SQL will assign a new ID every time.

Why some object IDs are in negative and some IDs are in positive ?

select * from sys.all_objects

If you execute above query, you can see Some IDs are in positive and some are in negative. All user defined objects are assigned a positive object ID plus system tables. Apart from these objects, the rest of the system objects are assigned negative object IDs.

All the above facts and figures can vary in different versions of SQL. If you know something new about Object_ID do share with me.

Read Full Post »