CODEWITHSUNDEEP
sql-serversystem-tables
SergB
SergB

Reputation: 91

SQL Server system table identity crysis / SYSOBJECTS table ID is close to int max value

SYSOBJECTS table has an identity column of int type. But values in ID column are getting close to int max value. I guess it'll become impossible to create any objects when it reaches its maximum.

Is there a way to alter that table to convert ID column to BIGINT for example? Or are there any tools that can solve this problem?

Upvotes: 0

Views: 140

Answers (2)

Martin Smith
Martin Smith

Reputation: 453298

you have nothing to worry about.

Object ids are not assigned sequentially. They have a gap of 16000057 between each one and just wrap around on overflow (approx every few hundred object creations) so it is normal you will see some big numbers in there.

Upvotes: 3

Thom A
Thom A

Reputation: 95557

This won't fit (well) in a comment.

From Maximum Capacity Specifications for SQL Server:

SQL Server Database | Maximum sizes/numbers | Additional Information 
Engine object       | SQL Server (64-bit)   |
--------------------|-----------------------|----------------------------------------------------
Tables per database | Limited by number of  | Database objects include objects such as tables,
                    | objects in a database | 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 are close to 2,147,483,647 objects, you need to fix your design. End of story.

Upvotes: 0

Related Questions