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.
Didn’t know that pattern of increment of Object_ID. Thanks a lot !
Do you happen to know what object_id = 1 mean?
Unless I am reading this wrong – “PAGE: 62:1:659487”:
I just created a new database in my SQL Server. And the first table I created in it got the OBJECT_ID of 245,575,913.
In fact, I can’t understand how the 1st object_id could be 2,073,058,421 and the increment 16,000,057 when SQL Server object ids are int (i.e. 32bit, max = 2,147,483,647). It would take only 5 objects to exeed the int32 limit.
[…] SQL SERVER – ALL ABOUT OBJECT_ID […]
[…] 2013 (Muhammad Imran): https://raresql.com/2013/01/29/sql-server-all-about-object_id/ […]
[…] any negative value wrapping around to max signed int. (The significance of 16000057 is that this is the increment applied between successively created object_id). This still guarantees […]