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 tye | Range | Storage |
bigint | 9,223,372,036,854,775,807 | 8 Bytes |
int | 2,147,483,647 | 4 Bytes |
smallint | 32,767 | 2 Bytes |
tinyint | 255 | 1 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]