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

Last twelvemonth nosotros had a production number where 1 of our backup jobs was failing patch inserting Orders aggregated from other systems into our SQL Server database. The argue was dreaded "Arithmetic overflow fault 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 around 2.1 billion, exactly 2,147,483,647. The fault "Arithmetic overflow fault 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 current value of Identity becomes to a greater extent than than 2,147,483,647, together with so yous 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 fault 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, yous volition acquire "Arithmetic overflow fault converting IDENTITY to information type smallint" if identity value crosses 32,767 which is the maximum value for smallint inward SQL Server.

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

Btw, if yous are non familiar amongst the attain of basic information types inward SQL Server, I strongly propose yous boot the bucket through a course of teaching like Microsoft SQL Server For Beginners to larn fundamentals. Such cognition goes a long agency inward debugging together with troubleshooting this form of work inward production. This is 1 of my favorite SQL Server course of teaching on Udemy together with it covers all the cardinal concepts a programmer or a DBA needs to know most SQL Server.

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

The Problem

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

Troubleshooting

First affair commencement is to uncovering out where exactly the fault is occurring, similar which column, which tabular array together with which database. Unfortunately, SQL Server errors non really accurate, but they are neat at all. They volition probable say yous which stored physical care for yous were running together with which line of SQL caused this error. By next those traces, yous tin give notice locate the column where information insertion is failing.

Once yous flora the column, yous tin give notice confirm the information type, similar if yous are getting "Arithmetic overflow fault converting IDENTITY to information type tinyint" fault together with so most probable your column would direct maintain tinyint every bit a information type. Similarly, it could move an int or minor int.

After that, nosotros take away to uncovering the electrical current value of IDENTITY for that table, together with for that, nosotros take away to utilization DBCC tool every bit shown below:

DBCC CHECKIDENT('Audit.OrderDetails')

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

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

Btw, yous may non move able to run that ascendence inward production, every bit yous may non direct maintain relevant permissions. In that case, merely include your Database admins or DBAs. I also propose yous boot the bucket through the SQL Server Administration - Part 1 course of teaching on Udemy to larn most tools similar DBCC which is really useful patch 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 fighting 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 current rows inward the tabular array is less than the attain 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 entirely 1 billion rows there, but IDENTITY value is already 2147483647 together with so yous tin give notice reseed the IDENTITY to direct maintain wages of the gap betwixt an actual number of rows together with the electrical current value of IDENTITY.

But, for reseeding yous either take away to drib the tabular array or truncate it together with for that argue it's ameliorate to re-create the information into a temporary tabular array together with 1 time the IDENTITY is reseeded, re-create it 1 time again into the master copy tabular array every bit 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 directly direct maintain IDENTITY values starting from one. You tin give notice also confirm the maximum value for your IDENTITY column past times using the MAX function, every bit shown below:

SELECT MAX(OrderId) FROM OrderDetails

This volition give yous a proficient idea, how much your tabular array tin give notice grow farther without breaking amongst "Arithmetic overflow fault converting IDENTITY to information type tinyint" or "Arithmetic overflow fault converting IDENTITY to information type smallint" error.

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

If yous 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 attain every bit SQL Server int, i.e. (2,147,483,647) together with so a large value volition non check into it together with it volition overflow into a negative value, which tin give notice elbow grease issue.

Similarly, reseeding IDENTITY tin give notice also elbow grease a work if that value is used to generate something else. Having similar value may number inward duplicate Ids inward another system.

So, fifty-fifty though the solution of "Arithmetic overflow fault converting IDENTITY to information type tinyint" is simple, it tin give notice move complicated to solve inward a real-world scenario. It won't move slowly to increment the attain if your tabular array is essential, together with contains information which yous cannot lose, together with many clients are using that information live. 

Though, a proficient cognition of SQL Server itself comes handy patch 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 yous intend similar this together with so yous should banking company 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 yous similar my solution together with explanation of "Arithmetic overflow fault converting IDENTITY to information type int" fault inward SQL Server, together with so delight portion amongst your friends together with colleagues. If yous direct maintain whatever questions or feedback, together with so delight drib a note.

P.S - If yous desire to larn Microsoft SQL Server from scratch,  yous should banking company check out Querying Microsoft SQL Server amongst Transact-SQL course, 1 of the best resources to master copy MSSQL inward depth. It's also really useful if yous 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".