Before we actually look at the answer, let’s recall some basics of the IDENTITY property and SQL Server’s numerical data types.
You can define the IDENTITY property on columns of the INT data type and on DECIMAL with scale 0. This gives you a range of:
|
TINYINT |
0 – 255 |
|
SMALLINT |
-32.768 – 32.767 |
|
INT |
-2.147.483.648 – 2.147.483.647 |
|
BIGINT |
-2^63 – 2^63-1 |
When you decide to use the DECIMAL datatype you have a potential range from -10^38 to 10^38-1.
So, keeping this in mind, we’re now ready to answer the original question here. What happens when an INTEGER IDENTITY value is about to run out of scope?
CREATE TABLE id_overflow ( col1 INT IDENTITY(2147483647,1) ) GO INSERT INTO id_overflow DEFAULT VALUES INSERT INTO id_overflow DEFAULT VALUES SELECT * FROM id_overflow DROP TABLE id_overflow (1 row(s) affected) Server: Msg 8115, Level 16, State 1, Line 2 Arithmetic overflow error converting IDENTITY to data type int. Arithmetic overflow occurred.This script creates a simple table with just one column of type INT. We have also created the IDENTITY property for this column. But instead of now adding more than 2 billion rows to the table, we rather set the seed value to the positive maximum value for an INTEGER. The first row inserted is assigned that value. Nothing unusual happens. The second insert, however, fails with the above error. Apparently SQL Server does not start all over again or tries to fill the maybe existing gaps in the sequence. Actually, SQL Server does nothing automatically here. You have to do this by yourself. But what can you do in such a case?
Probably the easiest solution is to alter the data type of the column to BIGINT, or maybe right on to DECIMAL(38,0) like so:
CREATE TABLE id_overflow ( col1 INT IDENTITY(2147483647,1) ) GO INSERT INTO id_overflow DEFAULT VALUES ALTER TABLE id_overflow ALTER COLUMN col1 BIGINT INSERT INTO id_overflow DEFAULT VALUES SELECT * FROM id_overflow DROP TABLE id_overflow col1 ——————– 2147483647 2147483648 (2 row(s) affected)If you know in advance that your table needs to keep that many rows, you can do:
CREATE TABLE bigint_t ( col1 BIGINT IDENTITY(-9223372036854775808, 1) ) GO INSERT INTO bigint_t DEFAULT VALUES SELECT * FROM bigint_t DROP TABLE bigint_t col1 ——————– -9223372036854775808 (1 row(s) affected)Or the DECIMAL(38,0) variation:
CREATE TABLE decimal_t ( col1 DECIMAL(38,0) IDENTITY(-99999999999999999999999999999999999999, 1) ) GO INSERT INTO decimal_t DEFAULT VALUES SELECT * FROM decimal_t DROP TABLE decimal_t col1 —————————————- -99999999999999999999999999999999999999 (1 row(s) affected)One might be distressed in one’s aesthetical taste by those negative numbers, but it’s a fact, that one now shouldn’t have to worry about running out of scope for quite some time.
]]>