Max Size of SQL Server Auto-Identity Field

sql auto-increment

An INT will take you up to 2,147,483,647.

A BIGINT will get you 9,223,372,036,854,775,807.

sql auto-increment

2^31 - 1 (2,147,483,647) is the upper range of an int

sql auto-increment

The limit is related to the datatype itself, not with the fact of being auto increment.

If you are concerned about the maximum size, you can start the sequence below zero, and thus double the capacity, like this:

CREATE TABLE [MYTABLE]([ID] [int] IDENTITY(-2147483648,1),    (...)

CodeHunter