Wednesday, August 3, 2016

Arithmetic overflow error converting expression to data type int - It works for larger number but not for a smaller number

One of my colleagues sent me a simple code that has two lines. The first line works without any issue but second throws an error. This is the code;

SELECT 9*10000000000000000
SELECT 9*1000000000

This is the output;


What is the issue, the first calculation is much larger to second one but it works. The reason is, how SQL Server evaluates the expression and decides the data types. If we consider the second line, the largest value in the expression is 1,000,000,000 which is lower to the highest value that int can hold. The maximum number int can hold is 2,147,483,647. Since both 9 and 1,000,000,000 fall into the range related int, it sets the data type of the return value has int as well. However, when 1,000,000,000 gets multiplied by 9, it exceeds the range. That is the reason for the error.

With the first line, 10,000,000,000,000,000 is considered as a bigint type number and the result becomes the same. Therefore it works without any issue.

If we write the query as below, it will work without any issue.

DECLARE @a bigint
SET @A = 10000000000000000
SELECT  9*@A

SET @A = 1000000000
SELECT  9*@A

No comments: