Maximum for autoincremental int primary key in SqlServer

Question

Is there a maximum? Will SQL Server start throwing SqlExceptions when maximum is reached? How do you solve it? (archiving?)

From the SQL Server resources I can see that the maximum is 2,147,483,647. I'm far from that, but I was just curious.

Answer 1

The maximum for an int is indeed 2,147,483,647.

If you attempt to go past the maximum size, you will receive this error:

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

If that is not big enough, use a bigint (9,223,372,036,854,775,807)

Answer 2

you can see the error using this small example

use tempdb;

if OBJECT_ID('dbo.test', 'U') is not null drop table dbo.test

create table test
( id int identity not null,
  dummy int not null )
go

SET IDENTITY_INSERT dbo.test ON

insert into test(id, dummy) values(2147483647, 1)

SET IDENTITY_INSERT dbo.test OFF

insert into test(dummy) values(1)

the error:

(1 row(s) affected)
Msg 8115, Level 16, State 1, Line 8
Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.