Int Primary key - exceeding int range

database-design, sql-server, sql-server-2008

Solution

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

Problem

Just wondering what actually happens after you exceed 2147483647 records?

Original source