Int Identity Column Outgrow Fix

When you use INT data type as identity column, you may run out of the positive limit (2147483647 or 2^31-1, which is a very large number and it is not common to reach it) in rare cases.
Besides you can schedule a process to change the column data type to BIGINT 9223372036854775807 (or 2^63-1),you can use the following quick fix to utilize the negative range of int numbers.

 DBCC CHECKIDENT('[dbo].[ALogTable]', RESEED, -2147483648)


The change data type for identity column may involve script out your key, constraints and index information and drop these keys/index on the table and use
Alter yourtable alter column theidentity bigint;
Reapply keys and constraints and rebuild index.
The process needs to be tested on lower environments to make sure it will work with your production environment.


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s