Arithmetic overflow error converting IDENTITY to data type

How to avoid the arithmetic overflow error when converting IDENTITY to INT data type with SQL Server?

In T-SQL, the arithmetic overflow error when converting IDENTITY to INT data type is an error message that appears when attempting to insert a new row into a table with an IDENTITY column and the value being assigned to the IDENTITY column exceeds the maximum value that can be stored in a SQL Server INT data type, which is 2147483647.

Table of Contents

1. Arithmetic overflow during a standard insertion

To start, create the first test table and insert 3 different rows into it.

DROP table TestTable;

CREATE TABLE TestTable (
    ID   INT IDENTITY(2147483647,1) PRIMARY KEY,
    Col1 INT
);

SET IDENTITY_INSERT TestTable OFF;

-- The insertion is working
INSERT INTO TestTable (Col1)
VALUES (1);

-- The insertion is not working
INSERT INTO TestTable (Col1)
VALUES (2);

The last insert will fail with the following message:

Arithmetic overflow error converting IDENTITY to data type int.

To work around this error, change the SQL Server data type of the IDENTITY column to a larger data type, such as BIGINT.

DROP table TestTable;

CREATE TABLE TestTable (
    ID   BIGINT IDENTITY(2147483647,1) PRIMARY KEY,
    Col1 INT
);

SET IDENTITY_INSERT TestTable OFF;

-- The insertions are working now
INSERT INTO TestTable (Col1) VALUES (1);
INSERT INTO TestTable (Col1) VALUES (2);
INSERT INTO TestTable (Col1) VALUES (3);

2. Arithmetic overflow with IDENTITY INSERT option ON

Inserting a row into a table with an IDENTITY column after using the SET IDENTITY_INSERT statement, which specifies that the column marked with the IDENTITY option should be included in the insert.

This second scenario is similar, but the explicit insertion option of the identity type column is enabled. It is therefore necessary to:

CREATE TABLE TestTable2 (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Col1 INT
);

SET IDENTITY_INSERT TestTable2 ON;

-- This code fails with the arithmetic overflow error message
INSERT INTO TestTable2 (ID, Col1)
VALUES (2147483648, 1);

A possible workaround for this common conversion error is to change the data type of the IDENTITY column to a larger data type, like the BIGINT for example.

DROP TABLE IF EXISTS TestTable2;

CREATE TABLE TestTable2 (
    ID BIGINT IDENTITY(1,1) PRIMARY KEY,
    Col1 INT
);

SET IDENTITY_INSERT TestTable2 ON;

-- The insert statement works now
INSERT INTO TestTable2 (ID, Col1)
VALUES (363372036854775807, 1);

Arithmetic overflow error converting expression

3. Arithmetic overflow due to first value

Here is a third example of T-SQL code that could generate the arithmetic overflow error.

CREATE TABLE TestTable3 (
    ID INT IDENTITY(2147483647,1) PRIMARY KEY,
    Col1 INT
);

INSERT INTO TestTable3 (Col1)
VALUES (1);

-- This insert will fail with the arithmetic overflow error message
INSERT INTO TestTable3 (Col1)
VALUES (1);

he IDENTITY property is used to indicate that the “id” column is an identity column, which means that a unique value will be automatically assigned to it for each new row inserted into the table. In this example, the IDENTITY property is set to start at the maximum value for an INT data type (2147483647) and to increment by 1 for each new row.

This will cause an arithmetic overflow error when the next value to be assigned to the “id” column exceeds the maximum value that can be stored in an INT data type (2147483647).

To fix this error, you can either change the data type of the “id” column to a larger data type that can hold larger values (such as BIGINT), or you can specify a smaller start value and/or increment for the IDENTITY property.

This time, change the start number of the increment. This will create the “test” table with an “id” column that starts at 1 and increments by 1 for each new row, which should not cause an arithmetic overflow error. For example:

CREATE TABLE TestTable3 (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Col1 INT
);

INSERT INTO TestTable3 (Col1)
VALUES (1);

-- The insert now works
INSERT INTO TestTable3 (Col1)
VALUES (1);

On the other hand, a SQL Server select or insert query copies the data type of the columns from the source table to the created table and the values. However, some values used as IDs in some unsorted tables can quickly reach the largest integer allowed by the INT data type (integer).

4. Conclusion on IDENTITY arithmetic overflow

In conclusion, the arithmetic overflow error when converting IDENTITY to INT data type in T-SQL can occur when inserting a new row into a table. Indeed, with an IDENTITY column and the value assigned to the column exceeds the maximum value that can be stored in the integer data type.

To work around this error, change the data type of the IDENTITY column to a larger data type (such as BIGINT), or specify a smaller start value for the IDENTITY property. You can also adjust the increment if possible.