Maximum for autoincremental int primary key in SqlServer

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)

you can see the error using this small example

use tempdb;if OBJECT_ID('dbo.test', 'U') is not null drop table dbo.testcreate table test( id int identity not null,  dummy int not null )goSET IDENTITY_INSERT dbo.test ONinsert into test(id, dummy) values(2147483647, 1)SET IDENTITY_INSERT dbo.test OFFinsert into test(dummy) values(1)

the error:

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

The maximum is defined by the datatype, not by the IDENTITY modifier. For an INT column, you already figured out the maximum. If you need a data type with a greater range, BIGINT is the obvious choice, and it can very well be marked IDENTITY.

• TINYINT: 0 through 255
• SMALLINT: -32768 through 32767
• INT: -2147483648 through 2147483647
• BIGINT: -9223372036854775808 through 9223372036854775807

I would expect an error to be raised when trying to insert rows after hitting the maximum, as the IDENTITY implementation would still try incrementing with each insert.

While it would certainly be possible to archive rows to some other data store and start reusing identifiers (DBCC CHECKIDENT (jobs, RESEED, 0) will reset the counter), it is not something that SQL Server offers out-of-the-box. You will have to implement the logic for this yourself, and further you will have to consider what kind of trouble reusing identifiers might bring to your application. For a web application, for instance, will old URLs suddently point to new documents or return 404 errors?