Int Primary key - exceeding int range

Question

Just wondering what actually happens after you exceed 2147483647 records?

Answer 1

try it out

CREATE TABLE #tester (
    testerid INT IDENTITY(1, 1) not null CONSTRAINT pk_tester 
    PRIMARY KEY CLUSTERED)

DBCC checkident(#tester, reseed, 2147483647)

INSERT #tester DEFAULT VALUES 
INSERT #tester DEFAULT VALUES 

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

See also What To Do When Your Identity Column Maxes Out for a quick fix

Answer 2

Most people forget there is a negative side to int which is one bigger than the positive side. If you think you might outrun the positive int values just start your identity at the negative end of the range -2,147,483,648. Or to really play with new DBA start at 2,147,483,647 and step by -1.