Double the Capacity of your Int Autonumbers

One of the justified concerns when using an intidentity as your surrogate primary keys is that you’ll exceedthe capacity of the data type. e.g. if you accept the defaults,with your autonumbers seeded at 1 with an increment of 1, you havethe capacity to store 2,147,483,647 records. While that sounds likea lot of records, and it most certainly is far beyond the lifetimesize of most databases, it does have the potential of beingexhausted in massive databases, or databases that see lots ofrolled-back transactions (which still use up identity values). Ifit’s a realistic possibility that you’ll exceed 2 billion records,consider using one of the larger data types, such as a bigint.Avoid using the larger data types unless realistically necessary, however, asthere is a storage and I/O cost that needs to be factored in.

Another potential solution is to take advantage of the negativerange of the signed int. You could do this by seeding your identityvalues with -2147483648, incrementing from there. This will makeyour first record IDs less human friendly (e.g. CustomerID-214783648 instead of CustomerID 1), however it will double theidentity range available, offering up 4 billion+ identityvalues.

You could also do this in already existing and populated tablesby resetting the seed to a negative value, for instance

DBCC CHECKIDENT ('YourTableName', RESEED, -2147483648)

However this will lead to insert issues (as it’ll be inserting atthe head of the data if you’ve cluster indexed on your primarykey), and the ident will get reset the next time you call

DBCC CHECKIDENT('YourTableName')