How is the Object_id generated within SQL Server


I have done some research on this object_id. This object_id comes from the primary key of system table sys.sysschobjs.

The new object_id will increase 16000057 (a prime number) from last object_id. When the last object_id +16000057 is over the int maximum ( 2147483647), it will start with a new number before the difference between the new bigint number and the maximum int. This cycle will generate 134 or 135 new object_id for each cycle. The system has a maximum number of objects,  which is 2147483647.

Here is a sample to extend Erland’s example to make it clear.

---Primary number 16000057  


If (object_id('test','U') is not null)
drop table test


Create table test (tid int identity(1,1),id int, idlen int)

GO


CREATE TABLE scant(a int NOT NULL)
insert into test(id,idlen)
SELECT object_id('scant'), Len(cast(object_id('scant') as varchar(12)))
 
DROP TABLE scant
GO 600

declare @intMax int =2147483647

Select id, lead(id) Over(Order by tid)
, CAse WHen Cast(lag(id) Over(Order by tid) as bigint)+16000057  > 2147483647 then 'Yes' else '' End OverLimit
,CAse WHen Cast(lag(id) Over(Order by tid) as bigint)+16000057  > 2147483647 
then Cast(lag(id) Over(Order by tid) as bigint)+16000057 else null End theBigNum
,CAse WHen Cast(lag(id) Over(Order by tid) as bigint)+16000057  > 2147483647 
then Cast(lag(id) Over(Order by tid) as bigint)+16000057- @intMax-1 else null End NewNum

,CAse WHen Cast(lag(id) Over(Order by tid) as bigint)+16000057  > 2147483647 
then  0  else id-lag(id) Over(Order by tid) End diff, 
idlen from test  


 

I came across the number 16000057  from:

SQL SERVER – ALL ABOUT OBJECT_ID

https://social.msdn.microsoft.com/Forums/en-US/f73c2115-57f7-4cec-a95b-00c2d8252ace/objectid-recycled-?forum=transactsql&prof=required

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s