Fix “Arithmetic overflow error converting IDENTITY to data type…” in SQL Server

If you’re getting error “Msg 8115, Level 16 Arithmetic overflow error converting IDENTITY to data type…” error in SQL Server, it’s probably because you’re trying to insert data into a table when its IDENTITY column has reached its data type’s limit.

An IDENTITY column automatically increments the value that’s inserted with each new row. If the value being inserted is out of the range of the column’s data type, then the above error will occur.

Example of the Error

Here’s an example of code that results in the error:

INSERT INTO t1 VALUES ('Dog');

Result:

Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type tinyint.

In this case, my IDENTITY column uses the tinyint data type, which has a range of 0 to 255. The error implies that the IDENTITY column is trying to insert a value that’s higher than 255.

This would typically occur when we’ve already inserted 255 rows into the column, and now we’re trying to insert the 256th row.

Here’s what my table looks like when I select all rows where the IDENTITY column is greater than 250:

SELECT * FROM t1
WHERE c1 > 250;

Result:

+------+------+
| c1   | c2   |
|------+------|
| 251  | Ant  |
| 252  | Cow  |
| 253  | Bat  |
| 254  | Duck |
| 255  | Bull |
+------+------+

In this case, c1 is my IDENTITY column (which happens to be type tinyint). We can see that IDENTITY has previously generated 255 for the column, and so the next value that tries to insert is 256 (assuming an increment value of 1 and no previously failed inserts). This will cause the above error, because 256 is outside the range of a tinyint.

The same issue can occur with data types of smallint (maximum value of 32,767) or int (maximum value of 2,147,483,647). It could also happen with bigint if you inserted enough rows (over 9,223,372,036,854,775,807).

However, the IDENTITY value doesn’t always match the number of rows inserted. You can set a seed value when creating an IDENTITY column, and you can also set an increment value. Therefore, you could easily reach the upper limit much earlier than the number of inserts performed on the table, depending on the seed and increment values.

Also, deleting rows from a table doesn’t reset the IDENTITY value (although truncating a table does).

Therefore, you could still experience the above error even when there are far fewer rows in the table than what the IDENTITY column’s data type might suggest.

Solution

One solution is to change the data type of the IDENTITY column. For example, if it’s smallint, change it to int. Or if it’s already int, change it to bigint.

Another possible solution would be to reset the IDENTITY seed to a lower value. This would only work if you’ve either deleted a lot of rows from the table, or if the original seed value was much higher than 1.

For example, if the IDENTITY column is already an int, but the IDENTITY seed started at say 2,000,000,000, you could reset the IDENTITY seed to 1, which would allow for another 2 billion rows to be inserted.

Helpful Functions

Here are some functions that can be very helpful in identifying this issue:

• IDENT_CURRENT() – returns the last identity value generated for a specified table or view on an identity column.
• @@IDENTITY – Returns the last-inserted identity value in the current session.
• IDENT_SEED() – Returns the original seed of an identity column.
• IDENT_INCR() – Returns the increment value of an identity column.

Also, here are 3 Ways to Get a Column’s Data Type in case you’re not sure what the column’s data type is.

Same Error in Different Scenarios

The same error (Msg 8115) can also occur (with a slightly different error message) when you try to explicitly convert between data types and the original value is outside the range of the new type. See Fix “Arithmetic overflow error converting int to data type numeric” in SQL Server to fix this.

It can also occur when you use a function such as SUM() on a column, and the calculation results in a value that’s outside the range of the column’s type. See Fix “Arithmetic overflow error converting expression to data type int” in SQL Server to fix this.