SQL Server has a nasty surprise in store for the unwary. One day you wake up to find that inserts into a table are failing with an error like this:

Server: Msg 8115, Level 16, State 1, Line 1 Arithmetic overflow error converting
IDENTITY to data type int. Arithmetic overflow occurred.

What has happened here? The table was designed with the primary key pattern that we see in examples all over the web:

create table [Test]
(
  [TestID] int not null identity(1,1)
)

So TestID was declared as an int, SQL Server's 32-bit integer type, which can represent numbers up to 2,147,483,647. Once the identity value has passed this limit, inserts will fail with the error above until you do something about it.

The obvious and cleanest solution is increase the size of the identity column, maybe going from int -> bigint, which is 64-bit.

alter table [Test] alter column [TestID] bigint

This will require exclusive access to the table and if you've really got 2 billion rows in there, then it will take a significant amount of time and disc space to complete.

However there is another way... 'int' is a signed value and the initial declaration of the identity used the classic (1,1), meaning start at 1 and increment. But what about the negative values? If your application code can handle it, then you can instantly start using the "other" 2 billion identity values that range from -2,147,483,648 up to -1:

dbcc checkident ('Test', reseed, -2147483648)

This command completes quickly, even on a table with a billion rows, and lets you get the database up and running again quickly so you can plan for a more controlled migration to a larger identity column.