Arithmetic overflow occurred

  • Arithmetic overflow error converting IDENTITY to data type tinyint.
  • Arithmetic overflow error converting IDENTITY to data type smallint.
  • Arithmetic overflow error converting IDENTITY to data type int.
  • Arithmetic overflow error converting IDENTITY to data type bigint.

We usally have an identity field in database structures. Sometimes tinyint, sometimes smallint, sometimes int and sometimes bigint. There is a maximum value for each type. And if you exceed this maximum value, you will encounter an error.

Data tyeRangeStorage
bigint9,223,372,036,854,775,8078 Bytes
int2,147,483,6474 Bytes
smallint32,7672 Bytes
tinyint2551 Bytes

For example, if you work with tinyint. Once you add 255 rows, then want to delete all rows from table, But the counter of increment column is already set to 255, even after delete all rows. The increase is continuous and continues to increase until it is reset

If you encounter “Aritmetic overflow error”, you should reset the identity column.

To solve this, you need to reset counter of increment column after deleting all rows. You can use below query when deleting all rows, so that counter will reset to reset to 0. After execute this query, you can add rows in table with increment column values as 1 or what you set at table design time.

DELETE FROM [YourTable]

DBCC CHECKIDENT ('[YourTable]', RESEED, 0)
GO

Or, you can use truncate table command, if you want to delete all rows from table. Truncate Command also reset increment column to initial value.

Truncate Table [YourTable]

Add a Comment

Your email address will not be published. Required fields are marked *