10

Can someone clarify for me why do I get an error when I try to set the variable @a in the example below?

DECLARE @a BIGINT
SET @a = 7*11*13*17*19*23*29*31
/*
ERROR:
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
*/

What I could figure out til now is that, internaly, SQL starts doing the math evaluating the multiplication and placing the temporary result into a INT then it casts it to a BIGINT.

However, if I add a 1.0 * to my list of numbers, there is no error, hence I believe that for this time SQL uses float as a temporary result, then cast it to BIGINT

DECLARE @b BIGINT
SET @b =   1.0  *  7*11*13*17*19*23*29*31
/*
NO ERROR
*/

Frankly, I don't see anything wrong with the code... it's so simple...

[ I am using SQL 2008 ]

[EDIT]

Thanks Nathan for the link. That's good information I didn't know about, but I still don't understand why do I get the error and why do I have do "tricks" to get a simple script like this working.

Is it something that I should know how to deal with as a programmer?

Or, this a bug and, if so, I will consider this question closed.

Improve this question
3
  • I would assume you are correct. Why should SQL infer 7 is a bigint rather than an int? Commented Feb 16, 2011 at 16:12
  • It doesn't even think it's an int, it thinks it's numeric(1,0). Commented Feb 16, 2011 at 16:17
  • Nathan, you are right. The question is why do I get an error. Why is not SQL assigning the right data type when is needed? Commented Feb 16, 2011 at 17:33

2 Answers 2

Reset to default
10

When you're doing calculations like this, the individual numbers are stored just large enough to hold that number, ie: numeric(1,0). Check this out:

Caution
When you use the +, -, *, /, or % arithmetic operators to perform implicit or explicit conversion of int, smallint, tinyint, or bigint constant values to the float, real, decimal or numeric data types, the rules that SQL Server applies when it calculates the data type and precision of the expression results differ depending on whether the query is autoparameterized or not.

Therefore, similar expressions in queries can sometimes produce different results. When a query is not autoparameterized, the constant value is first converted to numeric, whose precision is just large enough to hold the value of the constant, before converting to the specified data type. For example, the constant value 1 is converted to numeric (1, 0), and the constant value 250 is converted to numeric (3, 0).

When a query is autoparameterized, the constant value is always converted to numeric (10, 0) before converting to the final data type. When the / operator is involved, not only can the result type's precision differ among similar queries, but the result value can differ also. For example, the result value of an autoparameterized query that includes the expression SELECT CAST (1.0 / 7 AS float) will differ from the result value of the same query that is not autoparameterized, because the results of the autoparameterized query will be truncated to fit into the numeric (10, 0) data type. For more information about parameterized queries, see Simple Parameterization.

http://msdn.microsoft.com/en-us/library/ms187745.aspx

Edit

This isn't a bug in SQL Server. From that same page, it states:

The int data type is the primary integer data type in SQL Server.

and

SQL Server does not automatically promote other integer data types (tinyint, smallint, and int) to bigint.

This is defined behavior. As a programmer, if you have reason to believe that your data will overflow the data type, you need to take precautions to avoid that situation. In this case, simply converting one of those numbers to a BIGINT will solve the problem. 

DECLARE @a BIGINT
SET @a = 7*11*13*17*19*23*29*CONVERT(BIGINT, 31)
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

OK, but I still think this defined behavior is a wrong one. I would understand this behavior if I wouldn't define the variable as BIGINT. But once I defined the variable as a BIGINT, SQL should do the rest.
Remember that everything on the right side of the = is evaluated first. That's the part that is throwing an error. Any programming environment would work right to left, just some might automatically promote to another integer data type.
And it's not the varaiable that is failing, it is the calculation. Don't complain how SQL Server does things like this, the way it designed is the way it is designed. Just becasuee this doesn't suit your particular prejudices, doesn't mean that the way it is designed is invalid. I'm sure they had good reasons for doing things this way. You need to live within the limitations of the database.
HLGEM, if no one would "complain" about how things are working, there would be no need of improvement. I think you agree at least that the message is not really clear. You can see this message in so many cases ...
@leoinfo what you need to consider is: DECLARE @a AS VARCHAR(10); SET @a = 1+2+3; should this set @a to '123' or to '6', what would result from @a = 1+(2+3)? SQL rarely will 'do the rest' for anything, it mainly just tries to be consistent in functionality.
7

In the first example SQL Server multiplies a list of INTs together, and discovers the result is too big to be an INT and the error is generated. In the second example, it notices there's a float so it converts all the INTs to floats first and then does the multiplication.

Similarly, you can do this:

DECLARE @a BIGINT,
        @b BIGINT

set @b = 1
SET @a = @b*7*11*13*17*19*23*29*31

This works fine because it notices there's a BIGINT, so it converts all the INTs to BIGINTs and then does the multiplication.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.