Jump to content

Sql Identity Column Issue Is This Really A Solution?


dotnetrockz

Recommended Posts

One of my colleagues shared the following experience.

Recently, the identity column on one of his tables reached the maximum value allowable for an INT data type (2,147,483,647). Obviously, this caused all sorts of errors, as the application was unable to insert records into the table. This is a customer-facing table, so the issue needed to be resolved quickly. Converting the column to a BIGINT would require a significant amount of down-time. The solution? Reseed the identity column to -2,147,483,648 and have the values count upward toward zero. This took only seconds and the app was back up immediately. This is, of course, a temporary solution; it allows the team to schedule a more convenient time to resolve the issue.

Here’s how to do this:

DBCC CHECKIDENT ('tableName', RESEED, -2147483648);

 

 

 

Link to comment
Share on other sites

That's the only possible solution kada except for changing the datatype to BigInt

 

Either reseed the identity to 0 and decrement by 1 eachtime or

 

or reseed the identity to -2147483648 and increment by 1

 

Link to comment
Share on other sites

nice thinking...
what if they run out of option on this one too?

This is a good option for now man.. If they are gonna run out of these numbers soon they will fall in the soup.. Instead of that they can remove the is identity property of this table so that the table will not have identity column and add a new one with bigint or copy the data in increments to a different table and finally switchover to this table.. There will be no down time as we'll in this approach.

Switchover in increments i m telling only because if there a lot of tables holding this as a foreign key then there will be no impact and it time consumption factor.. Switching to bigint is good because it will accept values between 2^-31 to 2^31.
Link to comment
Share on other sites

If you have fk from this table, it won't stand good with direct reseed.. But I don't think that's the case for now at least with you guys.

Typing from phone ignore typos

Link to comment
Share on other sites

×
×
  • Create New...