How are Sys.Object Object_IDs Allocated?

Question

How are Sys.Object Object_IDs Allocated?

dougjjj

SSCrazy

Points: 2081
More actions
November 6, 2013 at 4:25 pm

#279738

Hi,
We recently came across an issue in Oracle where the ID of new contraints hit the max value of the Integer field (ie 2 Billion) causing a lot of pain all around. The IDs auto-incremented and unused IDs aren't reused.
So that got me thinking about SQL Server and I was wondering how they are allocated. The Object_ID for SQL Objects is an Integer field, so we're limited to 4 Billion unique objects, but are the old object_ids that no longer exist re-used? Etc
I've been searching the web but my Google-Fu is failing me and I haven't come up with anything, does anyone have any details on how these are allocated?
Cheers

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Pearce SSCoach Points: 15999 More actions · Answer 1

http://technet.microsoft.com/en-us/library/ms143432.aspx

Database objects include objects such as tables, views, stored procedures, user-defined functions, triggers, rules, defaults, and constraints. The sum of the number of all objects in a database cannot exceed 2,147,483,647

If you create objects it seems SQL Server uses an increment of 16,000,057 and wraps around after reaching the 2,147,483,647 limit. I couldn't tell you why.

CREATE TABLE TestObjectID (ID INT IDENTITY(1, 1), ObjectID INT)

GO

CREATE TABLE A1 (ID INT);

CREATE TABLE A2 (ID INT);

CREATE TABLE A3 (ID INT);

CREATE TABLE A4 (ID INT);

CREATE TABLE A5 (ID INT);

INSERT INTO TestObjectID (ObjectID)

SELECT object_id FROM sys.tables WHERE name LIKE 'A%'

DROP TABLE A1

DROP TABLE A2

DROP TABLE A3

DROP TABLE A4

DROP TABLE A5

GO 100

SELECT

a.*,

a.ObjectID - b.ObjectID

FROM

TestObjectID a

LEFT JOIN

TestObjectID b

ON a.ID - 1 = b.ID

DROP TABLE TestObjectID;

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 2

Not sure how things are allocated, but databases are limited to the 2B object limit.

http://msdn.microsoft.com/en-us/library/ms143432%28v=sql.120%29.aspx

dan-572483 SSCoach Points: 15870 More actions · Answer 3

Assuming that some (hopefully many) rows are deleted before there are actually 2 billion of them, once the wrap-around event occurs will the IDENTITY feature use the next UNUSED integer, or is there the risk of a unique key violation error?

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 4

If it's a PK, you can't have a violation. However, not sure if values are reused.