Arithmetic overflow error for type int value - Исправление ошибок и поиск оптимальных решений проблем

If you’re receiving error Msg 8115, Level 16, Arithmetic overflow error converting expression to data type int in SQL Server, it could be that you’re performing a calculation that results in an out of range value.

This can happen when you use a function such as SUM() on a column, and the calculation results in a value that’s outside the range of the column’s type.

Example of the Error

Here’s an example of code that produces the error:

SELECT SUM(bank_balance) 
FROM accounts;

Result:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

In this case I used the SUM() function to get the sum of the bank_balance column, which has a data type of int.

The error occurred because the result of the calculation is outside the range of the int data type.

Here’s all the data in my table:

SELECT bank_balance 
FROM accounts;

Result:

+----------------+
| bank_balance   |
|----------------|
| 1300000000     |
| 1200000000     |
| 800500000      |
+----------------+

Those are some big bank balances… and adding the three of them results in a larger number than an int can handle (the int range is -2,147,483,648 to 2,147,483,647).

The Solution

We can deal with this error by converting the int column to a bigint when we run the query:

SELECT SUM(CAST(bank_balance AS bigint)) 
FROM Accounts;

Result:

3300500000

This time it worked.

You could also change the data type of the actual column for a more permanent solution.

In case you’re wondering, the bigint range is -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

Same Error in Different Scenarios

The same error (Msg 8115) can also occur (with a slightly different error message) when you try to explicitly convert between data types and the original value is outside the range of the new type. See Fix “Arithmetic overflow error converting int to data type numeric” in SQL Server to fix this.

The same error (Msg 8115) can also occur (with a slightly different error message) when you try to insert data into a table when its IDENTITY column has reached its data type’s limit. See Fix: “Arithmetic overflow error converting IDENTITY to data type…” in SQL Server for how to fix this.

Источник

With SQL Server, how to avoid this error : “Arithmetic overflow error converting expression to data type int”. Let’s insert some data into a table using a stored procedure for example. Facing this SQL Server error message can be disturbing. Even if SQL conversion errors are classical ones, finding the solution and apply the industry best practices to avoid them is very useful.

In this case, the stored procedure executing the query generates some dynamic T-SQL code. Then it executes the code and insert data from various tables into one single target table. It’s a typical SQL Server int arithmetic overflow conversion error.

How to avoid the SQL Server Arithmetic overflow error?

While running the same piece of T-SQL code independently, that is outside the stored procedure, directly into the SQL Server Management Studio windows, no error is faced. But when integrating the same code into the stored procedure, this error is thrown:

Msg 50000, Level 16, State 2, Procedure MyStoredProcedure, Line 123
Arithmetic overflow error converting expression to data type int.

After analysis, the conversion error message didn’t come from the SELECT clause. The SQL INSERT clause from the same T-SQL stored procedure is throwing the error. The root cause is the the insertion of a big integer value into the table. For integer Data Type in SQL Server, the number must be between -2^31 (-2 to the power of 31) and 2^31 (2 to the power of 31).

Namely the range for the INTEGER type is exactly between -2,147,483,648 and 2,147,483,648.

Use another data type to avoid the conversion error

The solution to avoid Arithmetic overflow error converting expression is to use a bigger data type. The solution to avoid this arithmetic overflow error is to change the data type from INT to BIGINT or DECIMAL(11,0) for example.

Please note that in this case the conversion error is because the integer number is too big. It can also be an insertion of a text value into an integer field. Check out the int, bigint, smallintand tinyint official documentation for SQL Server.

Other classical conversion questions

This article shows how to simply avoid an Arithmetic overflow error converting expression to int in SQL Server. It happens when inserting a bigger number in an integer data type. Check out more classical SQL Server errors like the insert of a duplicate key.

What is a SQL Server arithmetic overflow error?

An arithmetic overflow error is an error faced when converting from one SQL Server data type to another one. For example from a numeric figure to an integer because the integer type can handle smaller figures compared to the numeric type.

What’s the biggest number for a SQL Server INTEGER?

The biggest number for the SQL Server integer data type is from -2 to the power of 31 till 2 to the power of 31. It means from -2^31 to 2^31 which is exactly this range: from -2,147,483,648 to 2,147,483,648. To avoid conversion error, always consider the maximum value for the data and size the database and the data types accordingly.

What’s the difference between INT and BIGINT?

A bigint is an integer, i.e. a number without decimals, and in SQL Server it can go from -2^63 that is exactly -9,223,372,036,854,775,808 to 2^63-1 which is 9,223,372,036,854,775,807 and is using 8 Bytes in storage.
While an INTEGER is from -2^31 which equals -2,147,483,648 to 2^31-1 which is exactly 2,147,483,647. An INTEGER data is stored on 4 Bytes.

How to avoid conversion errors with SQL Server?

One good practice to avoid conversion errors during is to put in place controls for the inputs. And moreover make sure to control the values inserted in the database. For example, users can only enter valid dates in a date field.

Check out how to manage time difference in hours minutes and also seconds with T-SQL.

Источник

Remove From My Forums

Question
I have somewhere inside long query with many CTE the following scenario:
```
DECLARE @q_stock INT, @q_sum_all INT, @q_sum int, @q_nar INT
--all this values are calculated before inside some CTE and I guess they are int type

SET @q_stock=97000
SET @q_sum_all=136500
SET @q_sum=136500
SET @q_nar=40000
SELECT ((@q_stock-@q_sum_all+@q_sum)*@q_nar)/CAST(@q_sum as float)
--the problem is in this part:
SELECT (@q_stock-@q_sum_all+@q_sum)*@q_nar
```
Arithmetic overflow error converting expression to data type int.

Why this error is happened? Why sql doesn’t convert it to appropriate dataType if value is greater than INT since I didn’t cast it explicitly?

If I do this there is still error:
```
SELECT CAST((@q_stock-@q_sum_all+@q_sum) *@q_nar AS BIGINT)
```
But if I do this, it is ok:
```
SELECT CAST((@q_stock-@q_sum_all+@q_sum) AS BIGINT)*@q_nar
```
I have multiplication of 2 integers in many of my queires. Should i go over all my queries and convert to BIGINT value before multiply? It would take me days since I need to test everything after change.

Answers

I have 2 options, change operation order or use bigInt:
```
DECLARE @q_sum int, @q_nar INT, @qAll INT

SET @q_sum=136500
SET @q_nar=40000
SET @qAll=97000


1.SELECT CAST(@qAll AS BIGINT)*@q_nar/CAST(@q_sum AS FLOAT)

2.SELECT @qAll*(@q_nar/CAST(@q_sum AS FLOAT))

3.SELECT CAST(@qAll AS BIGINT)*@q_nar/@q_sum

4.SELECT @qAll*(@q_nar/@q_sum)--0
```
In case 4 the result is 0 — so changing operation order on some existing code is very dangerous. I must do test everywhere.

When just cast one operand to BigInt doesn’t affect anything, since end result(after division) is always appropriate for INT type. I don’t need tests in this case I guess.

Or do you see some situation where i could have problems with bigInt?
- Marked as answer by
  
  Friday, May 13, 2016 2:47 PM

Источник

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».

Источник

Introduction

MAXINT or INT_MAX is the highest number that can be represented by a given integer data type. In SQL Server this number for the INT data type is 2,147,483,647. The highest number you can store using the BIGINT data type is 9,223,372,036,854,775,807.

The question I would like to look at today is the following: How can I calculate MAXINT without utilizing another datatype.

Signed Numbers

A signed integer allows to store positive and negative numbers, while an unsigned integer can only represent positive numbers.
All signed integer data types use a single bit to indicate if the number is positive or negative. In a 32 bit integer like SQL Servers INT data type 31 bits are left to encode the actual number. That means we can store 2^31 different numbers. With zero being the smallest non negative number, the largest number for the INT data type is 2^31-1 = 2,147,483,647.

Calculations

The trouble with this formula is, that when trying to directly calculate this number, you will have an intermediate result that is out of the range of the data type.

[sql]
SELECT POWER(2,31)-1;
[/sql]

This SELECT statement will cause an arithmetic overflow error:

Msg 232, Level 16, State 3, Line 1
Arithmetic overflow error for type int, value = 2147483648.000000.

One of the ways to deal with that is to use a data type that can store larger numbers like the float datatype:

[sql]
SELECT POWER(2.,31)-1
[/sql]

That works for the INT datatype. However, as there is no numerical datatype in SQL Server that uses more than 64 bits to represent a value, any attempt to do this for BIGINT will result in an inaccurate estimation of the real value:

[sql]
SELECT POWER(2.,63)-1
[/sql]

This results in 9,223,372,036,854,775,799 which is 8 under the accurate value of 9,223,372,036,854,775,807.

This raises the question I mentioned earlier: Is there a way to calculate MAXINT without using a different data type?

Binary Representations

From here on I will use MAXINT to represent the highest number for the SQL Server INT datatype and MAXBIGINT to do the same for the SQL Server BIGINT data type.

If you display MAXINT as binary number it looks like this

1111111111111111111111111111111

Each digit in a binary number represents a specific power of two. The right most digit in above number stands for 2^0 and the left most for 2^31. To calculate the decimal value for a binary number you just add the «power of two»-values for each digit multiplied with the value of that digit. So binary 1011 would be 1*2^3 + 0*2^2 = 1*2^1 + 1*2^0 = 8+0+2=1 = 11. That means, to get to MAXINT, you can just add all 31 powers of two from 2^0 to 2^30:

[sql]
SELECT SUM(POWER(2,n-1)) FROM dbo.GetNums(31);
[/sql]

The code uses Itzik Ben-Gan’s GetNums function to produce a list of all numbers from 1 to 31. That allows us to calculate the «power of 2»-values from 2^0 to 2^30 and add them all together using the SUM aggregate function.

The above solution works for MAXINT. It also works for MAXBIGINT if you first CAST the literal 2 to BIGINT. However, this method of calculation seems overly complicated.

Two’s Complement

So far we have only looked at positive numbers. We said already that negative numbers are represented by using a single bit as an indicator. This is done with the Two’s Complement encoding. I will not go into more detail about this encoding here, but it being used means that we can represent as many different negative as non negative numbers. As zero is not a negative number the possible values for negative numbers range from -(2^0) to -(2^31) for INT and -(2^0) to -(2^63) for BIGINT. So while we can’t represent 2^63, we can represent -(2^63) with the BIGINT data type.

To calculate -(2^63) we can use the fact that an uneven power of a negative number is again negative. That means that -(2^63) = (-2)^63. To get to MAXBIGINT we now just need to subtract this value from -1:

-1 - (-2)^63 = -1 - (-(2^63)) = -1 + 2^63

In T-SQL the calculation looks like this for MAXINT:

[sql]
SELECT -1-POWER(-2,31);
[/sql]

For MAXBIGINT we again need to first cast the literal 2 to BIGINT to tell SQL Server that this is the data type we would like to work in:

[sql]
SELECT -1-POWER(CAST(-2 AS BIGINT),63);
[/sql]

In above calculations it is important to keep the order of the steps. Writing -POWER(-2,31)-1, while mathematically equivalent will cause SQL Server to first calculate 2^31 which will instantly cause the dreaded Arithmetic Overflow error.

Conclusion

While there is no way to directly calculate MAXBIGINT in SQL Server due to data type limitations, exploiting the fact that the range for integer data types for numbers smaller that zero is one bigger than the range for numbers greater than zero allows us to calculate this value within the ranges of the BIGINT data type.

Источник

April 2, 2019
MSSQL

You can receive this error when you want to get a total result by using the SUM function. In order to avoid this error, you must convert the column in the SUM function to bigint type using CAST. Let’s clarify the subject by making an example.

Let’s create a table that has Name and Count columns and add a few records as follows. As you can see in the Script, the values we add to the Count column force the limits of the int data type.

CREATE TABLE [dbo].[ConvertExample](

[Name] [varchar](250) NULL,

[Count] [int] NULL

) ON [PRIMARY]

INSERT INTO [dbo].[ConvertExample] ([Name] ,[Count]) VALUES (‘Nurullah CAKIR’,2050512989)

INSERT INTO [dbo].[ConvertExample] ([Name] ,[Count]) VALUES (‘Hakan GURBASLAR’,2050512988)

INSERT INTO [dbo].[ConvertExample] ([Name] ,[Count]) VALUES (‘Faruk ERDEM’,2050512987)

INSERT INTO [dbo].[ConvertExample] ([Name] ,[Count]) VALUES (‘Dilara AYDIN’,2050512987)

When we sum count column with a query such as the following by using the SUM function, we will receive the error that is subject to our article because the total value will exceed the limit of the int type.

SELECT SUM(Count) FROM [dbo].[ConvertExample]

When we change the query as follows, you will see that we can get the sum we want without error.

SELECT SUM(CAST(Count AS BIGINT)) AS COUNT_Column_Bigint FROM [dbo].[ConvertExample]

Источник

Example of the Error

The Solution

Same Error in Different Scenarios

How to avoid the SQL Server Arithmetic overflow error?

Use another data type to avoid the conversion error

Other classical conversion questions

Question

Answers

The Problem

Troubleshooting

Solution

Summary

Introduction

Signed Numbers

Calculations

Binary Representations

Two’s Complement

Conclusion