How to solve Arithmetic overflow error converting IDENTITY to data type tinyint, smallint or int in Microsoft SQL Server database

Last year we had a production issue where one of our backup jobs was failing while inserting Orders aggregated from other systems into our SQL Server database. The reason was dreaded "Arithmetic overflow error converting IDENTITY to data type int" because the table was using IDENTITY feature of SQL Server to generate OrderId, and Identity has breached it a maximum value, which is around 2.1 billion, precisely 2,147,483,647. The error "Arithmetic overflow error converting IDENTITY to data type int" comes when the 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 the IDENTITY feature to automatically generate values.  For example, you will get "Arithmetic overflow error converting IDENTITY to data type smallint" if the 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.

Btw, if you are not familiar with the range of basic data types in SQL Server, I strongly suggest you go through a course like Microsoft SQL Server For Beginners to learn fundamentals. Such knowledge goes a long way in debugging and troubleshooting this kind of problem in production. This is one of my favorite SQL Server courses on Udemy and it covers all the fundamental concepts a programmer or a DBA needs to know about 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 that uses IDENTITY in SQL Server. It totally depends upon the data type of column but the error suggests that the problem is related to IDENTITY and values are out-of-range.

Troubleshooting

The 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, if you are getting an "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 a small int.

After that, we need to find the current value of IDENTITY for that table, and for that, we need to use the 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.

Btw, you may not be able to run that command in production, as you may not have relevant permissions. In that case, just include your Database admins or DBAs. I also suggest you go through the SQL Server Administration - Part 1 course on Udemy to learn about tools like DBCC which is very useful while working and troubleshooting problems like this in SQL Server.

Solution

There are two solutions to this problem -

1. First one is to increase the data type of column to bigint, a 64 bit int value in SQL Server which ranges from -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807).

2. Or, reseed the IDENTITY value if there are gaps in the value and current rows in the table is less than the range of values supported by that column.

For example, if your OrderId column which is causing the problem has an int data type, but there are only 1 billion rows there, but the IDENTITY value is already 2147483647 then you can reseed the IDENTITY to take advantage of the gap between an actual number of rows and the current value of IDENTITY.

But, for reseeding you either need to drop the table or truncate it, and for that reason it's better to copy the data into a temporary table and once the IDENTITY is reseeded, copy it again into the main table as shown below:

SELECT * INTO temp..OrderDetailsBackup FROM OrderDetails ORDER BY OrderId
 
TRUNCATE TABLE OrderDetails
 
DBCC CHECKIDENT (OrderDetails, RESEED,1)
 
INSERT INTO OrderDetails( ....) SELECT (....) FROM OrderDetailsBackup

By doing this, all the rows now have IDENTITY values starting from one. You can also confirm the maximum value for your IDENTITY column by using the MAX function, as shown below:

SELECT MAX(OrderId) FROM OrderDetails

This will give you a good idea, how much your table can grow further without breaking with the "Arithmetic overflow error converting IDENTITY to data type tinyint" or "Arithmetic overflow error converting IDENTITY to data type smallint" error.

Though, before applying any solution, like increasing the data type or reseeding the IDENTITY value, you need to perform due diligence. For example, if you are accessing that column into some other code then that could break.

If you increase the data type like if a Java code is accessing an int column and storing data into an int field, which has the same range as SQL Server int, i.e. (2,147,483,647) then a big value will not fit into it and it will overflow into a negative value, which can cause an issue.

Similarly, reseeding IDENTITY can also cause a problem if that value is used to generate something else. Having similar values may result in duplicate Ids in some other system.

So, even though the solution of "Arithmetic overflow error converting IDENTITY to data type tinyint" is simple, it can be complicated to solve in a real-world scenario. It won't be easy to increase the range if your table is essential, and contains data that you cannot lose, and many clients are using that data live. 

Though a good knowledge of SQL Server itself comes in handy while dealing with such issues in the real world, hence I suggest every programmer working in SQL Server learn some T-SQL and Administration functionality. If you think like this then you should check out SQL Server Fundamentals by Dan Sullivan's course on Pluralsight to actually learn this stuff.

Summary

1. "Arithmetic overflow error converting IDENTITY to data type int" error means the value of IDENTITY is overflowing range of data type of that particular column.

2. Check the current value of Identity

3. Increase data type to bigint or reseed IDENTITY
DBCC CHECKIDENT (OrderDetails, RESEED,1)


That's all about how to solve the "Arithmetic overflow error converting IDENTITY to data type int" error in Microsoft SQL Server. As I said, you have two options either increase the data type or make use of unused identities to keep the value in the range. If you don't care about duplicates then simply reseeding Identity can also work.


Other SQL and Database Articles you may like

• 5 Websites to learn SQL for FREE (websites)
• 5 Free Courses to Learn MySQL database (courses)
• 50+ SQL Server Phone Interview Questions with Answers (list)
• 5 Free Courses to learn Database and SQL (courses)
• 5 Books to Learn SQL Better (books)
• How to join more than two tables in a single query (article)
• Difference between WHERE and HAVING clause (answer)
• 10 SQL queries from Interviews (queries)
• Top 5 SQL books for Advanced Programmers (books)
• Difference between SQL, T-SQL, and PL/SQL? (answer)
• Top 5 Online Courses to Learn SQL and Database (courses)

Thanks for reading this article so far. If you like my solution and explanation of the "Arithmetic overflow error converting IDENTITY to data type int" error in SQL Server, then please share with your friends and colleagues. If you have any questions or feedback, then please drop a note.

P.S - If you want to learn Microsoft SQL Server from scratch,  you should check out these free T-SQL and SQL Server courses, one of the best resources to master MSSQL in depth. It's also very useful if you are preparing for SQL Server certifications like Microsoft Certificate 70-461: "Querying Microsoft SQL Server 2012" and 70-761 "Querying Data with Transact-SQL".