Int Primary key - exceeding int range
Just wondering what actually happens after you exceed 2147483647 records?
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
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.
If you think that is likely then use a datatype with a larger range. NUMERIC or BIGINT for example.
精彩评论