Continuing my recent trend of playing with large numbers, I recently boiled an error I was running into down to the following code:
DECLARE @big_number DECIMAL(38,0) = '1' + REPLICATE(0, 37);
PRINT @big_number + 1;
PRINT @big_number - 1;
PRINT @big_number * 1;
PRINT @big_number / 1;
The output I get for this code is:
10000000000000000000000000000000000001
9999999999999999999999999999999999999
10000000000000000000000000000000000000
Msg 8115, Level 16, State 2, Line 6
Arithmetic overflow error converting expression to data type numeric.
What?
Why would the first 3 operations work but not the last? And how can there be an arithmetic overflow error if @big_number
can obviously store the output of @big_number / 1
?
Best Answer
Understanding Precision and Scale in the context of Arithmetic Operations
Let's break this down and take a close look at the details of the divide arithmetic operator. This is what MSDN has to say about the result types of the divide operator:
We know that
@big_number
is aDECIMAL
. What data type does SQL Server cast1
as? It casts it to anINT
. We can confirm this with the help ofSQL_VARIANT_PROPERTY()
:For kicks, we can also replace the
1
in the original code block with an explicitly typed value likeDECLARE @one INT = 1;
and confirm we get the same results.So we have a
DECIMAL
and anINT
. SinceDECIMAL
has a higher data type precedence thanINT
, we know the output of our division will be cast toDECIMAL
.So where's the problem?
The problem is with the scale of the
DECIMAL
in the output. Here is a table of rules about how SQL Server determines the precision and scale of results obtained from arithmetic operations:And here's what we have for the variables in this table:
Per the asterisk comment on the table above, the maximum precision a
DECIMAL
can have is 38. So our result precision gets cut down from 49 to 38, and "the corresponding scale is reduced to prevent the integral part of a result from being truncated." It is not clear from this comment how the scale is reduced, but we do know this:According to the formula in the table, the minimum possible scale you can have after dividing two
DECIMAL
s is 6.Thus, we end up with the following results:
How this Explains the Arithmetic Overflow
Now the answer is obvious:
The output of our division gets cast to
DECIMAL(38, 6)
, andDECIMAL(38, 6)
cannot hold 1037.With that, we can construct another division that succeeds by making sure the result can fit in
DECIMAL(38, 6)
:The result is:
Note the 6 zeros after the decimal. We can confirm the result's data type is
DECIMAL(38, 6)
by usingSQL_VARIANT_PROPERTY()
as above:A Dangerous Workaround
So how do we get around this limitation?
Well, that certainly depends on what you're making these calculations for. One solution you may immediately jump to is to convert your numbers to
FLOAT
for the calculations, and then convert them back toDECIMAL
when you're done.That may work under some circumstances, but you should be careful to understand what those circumstances are. As we all know, converting numbers to and from
FLOAT
is dangerous and may give you unexpected or incorrect results.In our case, converting 1037 to and from
FLOAT
gets a result that is just plain wrong:And there you have it. Divide carefully, my children.