How To Solve Arithmetics Overflow Mistake Converting Identity To Information Type Tinyint, Smallint Or Int Inwards Microsoft Sql Server Database

Last twelvemonth nosotros had a production number where 1 of our backup jobs was failing spell inserting Orders aggregated from other systems into our SQL Server database. The argue was dreaded "Arithmetic overflow mistake converting IDENTITY to information type int" because the tabular array was using IDENTITY characteristic of SQL Server to generate OrderId, together with Identity has breached it a maximum value, which is unopen to 2.1 billion, exactly 2,147,483,647. The mistake "Arithmetic overflow mistake converting IDENTITY to information type int" comes when IDENTITY value is inserted into a column of information type int, but the value is out-of-range. For example, if the electrical flow value of Identity becomes to a greater extent than than 2,147,483,647, together with then you lot cannot shop that into an int column because it's to a greater extent than than the maximum value of int inward SQL Server.

The mistake is to a greater extent than mutual amongst columns using smaller datatypes similar SMALLINT, TINYINT, together with INT and uses IDENTITY characteristic to automatically generate values.  For example, you lot volition acquire "Arithmetic overflow mistake converting IDENTITY to information type smallint" if identity value crosses 32,767 which is the maximum value for smallint inward SQL Server.

Similarly, you lot volition acquire "Arithmetic overflow mistake converting IDENTITY to information type tinyint" if IDENTITY has grown beyond 255, the maximum value of tinyint information type inward SQL Server.

Btw, if you lot are non familiar amongst the make of basic information types inward SQL Server, I strongly propose you lot transcend through a course of pedagogy like Microsoft SQL Server For Beginners to larn fundamentals. Such cognition goes a long agency inward debugging together with troubleshooting this variety of work inward production. This is 1 of my favorite SQL Server course of pedagogy on Udemy together with it covers all the primal concepts a programmer or a DBA needs to know almost SQL Server.

Anyway, let's plow dorsum our focus on how to solve this problem.

The Problem

You are getting "Arithmetic overflow mistake converting IDENTITY to information type int," or mayhap "Arithmetic overflow mistake converting IDENTITY to information type smallint," or "Arithmetic overflow mistake converting IDENTITY to information type tinyint" spell inserting information into a tabular array which uses IDENTITY inward SQL Server. It totally depends upon the information type of column but mistake suggests that the work is related to IDENTITY together with values are out-of-range.

Troubleshooting

First affair showtime is to notice out where exactly the mistake is occurring, similar which column, which tabular array together with which database. Unfortunately, SQL Server errors non rattling accurate, but they are groovy at all. They volition probable state you lot which stored physical care for you lot were running together with which line of SQL caused this error. By next those traces, you lot tin post away locate the column where information insertion is failing.

Once you lot constitute the column, you lot tin post away confirm the information type, similar if you lot are getting "Arithmetic overflow mistake converting IDENTITY to information type tinyint" mistake together with then most probable your column would receive got tinyint equally a information type. Similarly, it could last an int or modest int.

After that, nosotros necessitate to notice the electrical flow value of IDENTITY for that table, together with for that, nosotros necessitate to usage DBCC tool equally shown below:

DBCC CHECKIDENT('Audit.OrderDetails')

This volition impress something like:
Checking identity information: electrical flow identity value '11762933', electrical flow column value '11762933'.
DBCC execution completed. If DBCC printed mistake messages, contact your organisation administrator.

If this value is out-of-range, together with then it confirms that the IDENTITY value is causing the problem.

Btw, you lot may non last able to run that ascendancy inward production, equally you lot may non receive got relevant permissions. In that case, only include your Database admins or DBAs. I also propose you lot transcend through the SQL Server Administration - Part 1 course of pedagogy on Udemy to larn almost tools similar DBCC which is rattling useful spell working together with troubleshooting problems similar this inward SQL Server.

Solution

There are 2 solutions to this work -

1. First 1 is to increase the information type of column to bigint, a 64 flake int value inward 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 at that topographic point are gaps inward the value together with electrical flow rows inward the tabular array is less than the make of values supported past times that column.

For example, if your OrderId column which is causing work has an int information type, but at that topographic point are exclusively 1 billion rows there, but IDENTITY value is already 2147483647 together with then you lot tin post away reseed the IDENTITY to accept wages of the gap betwixt an actual number of rows together with the electrical flow value of IDENTITY.

But, for reseeding you lot either necessitate to driblet the tabular array or truncate it together with for that argue it's amend to re-create the information into a temporary tabular array together with in 1 trial the IDENTITY is reseeded, re-create it over again into the psyche tabular array equally 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 straight off receive got IDENTITY values starting from one. You tin post away also confirm the maximum value for your IDENTITY column past times using the MAX function, equally shown below:

SELECT MAX(OrderId) FROM OrderDetails

This volition give you lot a proficient idea, how much your tabular array tin post away grow farther without breaking amongst "Arithmetic overflow mistake converting IDENTITY to information type tinyint" or "Arithmetic overflow mistake converting IDENTITY to information type smallint" error.

Though, earlier applying whatever solution, similar increasing the information type or reseeding the IDENTITY value, you lot necessitate to perform due diligence. For example, if you lot are accessing that column into another code together with then that could break.

If you lot increment the information type, similar if a Java code is accessing an int column together with storing information into an int field, which has same make equally SQL Server int, i.e. (2,147,483,647) together with then a large value volition non tally into it together with it volition overflow into a negative value, which tin post away crusade issue.

Similarly, reseeding IDENTITY tin post away also crusade a work if that value is used to generate something else. Having similar value may trial inward duplicate Ids inward another system.

So, fifty-fifty though the solution of "Arithmetic overflow mistake converting IDENTITY to information type tinyint" is simple, it tin post away last complicated to solve inward a real-world scenario. It won't last slow to increment the make if your tabular array is essential, together with contains information which you lot cannot lose, together with many clients are using that information live. 

Though, a proficient cognition of SQL Server itself comes handy spell dealing amongst such issues inward real-world, thus I propose every programmer working inward SQL Server to larn some T-SQL together with Administration functionality. If you lot mean value similar this together with then you lot should banking concern check out websites)

5 Free Courses to Learn MySQL database (courses)

50+ SQL Server Phone Interview Questions amongst Answers (list)

5 Free Courses to larn Database together with SQL (courses)

5 Books to Learn SQL Better (books)

How to bring together to a greater extent than than 2 tables inward a unmarried query (article)

Difference betwixt WHERE together with HAVING clause (answer)

10 SQL queries from Interviews (queries)

Top five SQL books for Advanced Programmers (books)

Difference betwixt SQL, T-SQL, together with PL/SQL? (answer)

Top five Online Courses to Learn SQL together with Database (courses)

Thanks for reading this article so far. If you lot similar my solution together with explanation of "Arithmetic overflow mistake converting IDENTITY to information type int" mistake inward SQL Server, together with then delight portion amongst your friends together with colleagues. If you lot receive got whatever questions or feedback, together with then delight driblet a note.

P.S - If you lot desire to larn Microsoft SQL Server from scratch,  you lot should banking concern check out Querying Microsoft SQL Server amongst Transact-SQL course, 1 of the best resources to master copy MSSQL inward depth. It's also rattling useful if you lot are preparing for SQL Server certifications like Microsoft Certificate 70-461: "Querying Microsoft SQL Server 2012" together with 70-761 "Querying Data amongst Transact-SQL". 

Share this post