Last year we had actually a production issue where among our backup jobs to be failing when inserting assignment aggregated from various other systems into our SQL Server database. The factor was dreaded "Arithmetic overflow error converting identification to data type int" since the table was making use of IDENTITY attribute of SQL Server to generate OrderId, and also Identity has actually breached the a best value, i beg your pardon is approximately 2.1 billion, exactly 2,147,483,647. The error "Arithmetic overflow error converting identification to data form int" comes once theIDENTITY value is put into a shaft of data form int, but the value is out-of-range. For example, if the present value of identity becomes an ext than 2,147,483,647, climate you cannot store that right into an int column due to the fact that it"s more than the maximum worth of int in SQL Server.

You are watching: Arithmetic overflow error converting expression to data type int.

The error is much more common v columns using smaller sized datatypes like SMALLINT, TINYINT, and INT anduses theIDENTITY function to automatically generate values. For example, girlfriend will get "Arithmetic overflow error converting identification to data kind smallint" if the identification value crosses 32,767 i m sorry is the maximum worth for smallint in SQL Server.Similarly, you will gain "Arithmetic overflow error converting identity to data type tinyint" if IDENTITY has grown beyond 255, the maximum value of tinyint data kind in SQL Server.Btw, if you space not familiar with the selection of basic data types in SQL Server, i strongly indicate you go v a food likeMicrosoft SQL Server because that Beginnersto discover fundamentals. Such understanding goes a long means in debugging and also troubleshooting this kind of trouble in production. This is just one of my favorite SQL Server process on Udemy and also it covers all the fundamental concepts a programmer or a DBA demands to know about SQL Server.Anyway, let"s turn earlier our emphasis on how to solve this problem.

The Problem

You are gaining "Arithmetic overflow error converting identification to data form int," or maybe "Arithmetic overflow error converting identity to data type smallint," or "Arithmetic overflow error converting identity to data form tinyint" when inserting data into a table the uses identification in SQL Server. It completely depends upon the data type of column yet the error suggests that the trouble is pertained to IDENTITY and also values are out-of-range.

Troubleshooting

The very first thing an initial is to uncover out where exactly the error is occurring, like which column, i m sorry table and also which database. Unfortunately, SQL Server errors not an extremely accurate, however they are not bad at all. They will likely tell friend which save on computer procedure you were running and which line of SQL led to this error. By following those traces, you can locate the column where data insertion is failing.Once you discovered the column, you deserve to confirm the data type, if you are getting an"Arithmetic overflow error converting identification to data kind tinyint" error climate most most likely your pillar would have tinyint as a data type. Similarly, it could be one int or a little int.After that, we require to discover the present value of identification for that table, and also for that, we must use theDBCC tool
as presented below:DBCC CHECKIDENT("Audit.OrderDetails")This will publish something like:

Checking identity information: current identity worth "11762933", current column worth "11762933".DBCC execution completed. If DBCC published error messages, call your system administrator.If this worth is out-of-range, then it confirms that the identification value is resulting in the problem.Btw, you might not be able to run the command in production, as you may not have actually relevant permissions. In the case, just incorporate your Database admins or DBAs. I likewise suggest girlfriend go through the SQL Server administration - part 1 course on Udemy come learn about tools prefer DBCC which is really useful when working and troubleshooting problems like this in SQL Server.

*

Solution

There room two options to this problem -1. An initial one is to boost the data type of column to bigint
, a 64 bit int worth in SQL Server which arrays 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 room gaps in the value and current rows in the table is less than the variety of values supported by the column.For example, if her OrderId shaft which is resulting in the trouble has one int data type, yet there are just 1 exchange rate rows there, but theIDENTITY worth is already 2147483647 climate you deserve to reseed the identification to take advantage of the gap between an actual variety of rows and the present value of IDENTITY.But, for reseeding friend either have to drop the table or truncate it, and for that reason it"s far better to copy the data into a momentary table and also once the identity is reseeded, copy that again right into the key table as presented below:
SELECT * right into temp..OrderDetailsBackup indigenous OrderDetails bespeak BY OrderIdTRUNCATE TABLE OrderDetailsDBCC CHECKIDENT (OrderDetails, RESEED,1)INSERT into OrderDetails( ....) select (....) indigenous OrderDetailsBackup
By law this, all the rows now have IDENTITY values starting from one. Girlfriend can additionally confirm the maximum worth for your identification column by utilizing the MAX function, as presented below:
This will offer you a great idea, how much her table can grow further there is no breaking through the"Arithmetic overflow error converting identity to data form tinyint" or "Arithmetic overflow error converting identity to data form smallint" error.Though, prior to applying any kind of solution, like boosting the data kind or reseeding the identification value, you have to perform early out diligence
. For example, if you are accessing that column right into some various other code climate that could break.If you boost the data form like if a Java code is accessing an int column and storing data right into an int field, which has actually the same range as SQL Server int, i.e. (2,147,483,647) then a huge value will certainly not fit into it and it will certainly overflow right into a an unfavorable value, i m sorry can reason an issue.Similarly, reseeding identification can additionally cause a difficulty if that value is offered to create something else. Having comparable values may result in duplicate Ids in some various other system.So, even though the solution of "Arithmetic overflow error converting identity to data type tinyint" is simple, it can be facility to solve in a real-world scenario.It won"t be easy to increase the variety if her table is essential, and contains data the you can not lose, and many clients are using that data live.Though a good knowledge the SQL Server itself comes in comfortable while handling such worries in the genuine world, thus I imply every programmer functioning in SQL Server find out some T-SQL and management functionality. If girlfriend think choose this climate you should examine outSQL Server Fundamentals through Dan Sullivan"s course on Pluralsight come actually find out this stuff.
*

Summary

1. "Arithmetic overflow error converting identity to data kind int" error way the worth of identity is overflowing range of data form of that details column.2. Inspect the current value that Identity3. Increase data form to bigint or reseed IDENTITYDBCC CHECKIDENT (OrderDetails, RESEED,1)That"s all around how to solve the "Arithmetic overflow error converting identification to data kind int" error
in Microsoft SQL Server. Together I said, you have two options either boost the data form or exploit unused identities to save the value in the range. If you don"t care about duplicates then just reseeding identification can also work.

See more: If You Wanna Be A Dog Vine Sticker Vinyl, The Garden Magazine

Other SQL and also Database Articles you may likeThanks for analysis this post so far. If you choose my solution and explanation the the "Arithmetic overflow error converting identification to data type int" error in SQL Server, then you re welcome share through your friends and colleagues. If girlfriend have any kind of questions or feedback, then you re welcome drop a note.P.S - If you desire to learn Microsoft SQL Server indigenous scratch, friend should check out these totally free T-SQL and SQL Server courses, one of the best resources to master MSSQL in depth. It"s also very useful if you space preparing for SQL Server certifications likeMicrosoft Certificate 70-461: "Querying Microsoft SQL Server 2012" and 70-761 "Querying Data with Transact-SQL".