The error "Arithmetic overflow error converting IDENTITY to data type int" comes when IDENTITY value is inserted into a column of data type int, but the value is out-of-range. For example, if the current value of Identity becomes more than 2,147,483,647, then you cannot store that into an int column because it's more than the maximum value of int in SQL Server.
The error is more common with columns using smaller datatypes like SMALLINT, TINYINT, and INT and uses IDENTITY feature to automatically generate values. For example, you will get "Arithmetic overflow error converting IDENTITY to data type smallint" if identity value crosses 32,767 which is the maximum value for smallint in SQL Server.
Similarly, you will get "Arithmetic overflow error converting IDENTITY to data type tinyint" if IDENTITY has grown beyond 255, the maximum value of tinyint data type in SQL Server.
Anyway, let's turn back our focus on how to solve this problem.
The Problem
You are getting "Arithmetic overflow error converting IDENTITY to data type int," or maybe "Arithmetic overflow error converting IDENTITY to data type smallint," or "Arithmetic overflow error converting IDENTITY to data type tinyint" while inserting data into a table which uses IDENTITY in SQL Server. It totally depends upon the data type of column but error suggests that the problem is related to IDENTITY and values are out-of-range.
Troubleshooting
First thing first is to find out where exactly the error is occurring, like which column, which table and which database. Unfortunately, SQL Server errors not very accurate, but they are not bad at all. They will likely tell you which stored procedure you were running and which line of SQL caused this error. By following those traces, you can locate the column where data insertion is failing.
Once you found the column, you can confirm the data type, like if you are getting "Arithmetic overflow error converting IDENTITY to data type tinyint" error then most likely your column would have tinyint as a data type. Similarly, it could be an int or small int.
After that, we need to find the current value of IDENTITY for that table, and for that, we need to use DBCC tool as shown below:
DBCC CHECKIDENT('Audit.OrderDetails')This will print something like:
Checking identity information: current identity value '11762933', current column value '11762933'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
If this value is out-of-range, then it confirms that the IDENTITY value is causing the problem.
Solution
An arithmetic overflow error occurs when a calculation in SQL Server produces a result that is too large to be stored in the specified data type. This error can occur when performing calculations on large numbers or when performing calculations that involve a large number of decimal places.
Here are some steps you can take to solve an arithmetic overflow error in Microsoft SQL Server:
STEP 1: Check the Data Types: Ensure that the data types of the columns involved in the calculation are appropriate for the values being used. For example, if a calculation involves a large number, ensure that the data type of the column is large enough to store the result.
STEP 2: Check the Precision and Scale: If the calculation involves decimal values, ensure that the precision and scale of the column are appropriate. If the scale is too large, it can cause an arithmetic overflow error.
STEP 3: Use CAST or CONVERT functions: If the data types are appropriate but the calculation is still causing an arithmetic overflow error, you can use the CAST or CONVERT functions to convert the result to a larger data type. For example, if the calculation involves multiplying two integers, you can cast one of the integers to a larger data type before performing the calculation:
SELECT CAST(Column1 AS BIGINT) * Column2 AS Result FROM MyTable STEP 4: Use the TRY_CAST or TRY_CONVERT functions: If the CAST or CONVERT functions result in a value that is too large for the specified data type, it will still cause an arithmetic overflow error. In this case, you can use the TRY_CAST or TRY_CONVERT functions to return NULL instead of raising an error:
SELECT TRY_CAST(Column1 AS BIGINT) * Column2 AS Result FROM MyTable In the above example, if the value of Column1 is too large for a BIGINT data type, the result will be NULL instead of raising an arithmetic overflow error.
STEP 5: Use the DECIMAL data type: If you are performing calculations on decimal values, you can use the DECIMAL data type with appropriate precision and scale. The DECIMAL data type allows you to specify the precision and scale of the number, which can help prevent arithmetic overflow errors.
By following these steps, you can identify and solve arithmetic overflow errors in Microsoft SQL Server. It is important to ensure that the data types, precision, and scale are appropriate for the values being used in calculations to avoid these errors.
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
order_amount INT
);
INSERT INTO Orders(order_id,customer_id,customer_id,order_amount)
VALUES
(1, 101, '2024-03-01', 100),
(2, 102, '2024-03-02', 150),
(3, 103, '2024-03-01', 200),
(4, 101, '2024-03-02', 750),
(5, 103, '2024-03-01', 550),
(6, 102, '2024-03-01', 750),
(7, 103, '2024-03-01', 550);
DROP TABLE Orders
SELECT * FROM Orders
SELECT order_id,
order_date,
COUNT() OVER (PARITION BY customer_id) AS Count_
FROM Orders;
SELECT * FROM Orders
SELECT order_id,