Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 16 down vote accepted

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:

Result Types

Returns the data type of the argument with the higher precedence. For more information, see Data Type Precedence (Transact-SQL).

If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.

We know that @big_number is a DECIMAL. What data type does SQL Server cast 1 as? It casts it to an INT. We can confirm this with the help of SQL_VARIANT_PROPERTY():

SELECT
      SQL_VARIANT_PROPERTY(1, 'BaseType')   AS [BaseType]  -- int
    , SQL_VARIANT_PROPERTY(1, 'Precision')  AS [Precision] -- 10
    , SQL_VARIANT_PROPERTY(1, 'Scale')      AS [Scale]     -- 0
;

For kicks, we can also replace the 1 in the original code block with an explicitly typed value like DECLARE @one INT = 1; and confirm we get the same results.

So we have a DECIMAL and an INT. Since DECIMAL has a higher data type precedence than INT, we know the output of our division will be cast to DECIMAL.

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:

Operation                              Result precision                       Result scale *
-------------------------------------------------------------------------------------------------
e1 + e2                                max(s1, s2) + max(p1-s1, p2-s2) + 1    max(s1, s2)
e1 - e2                                max(s1, s2) + max(p1-s1, p2-s2) + 1    max(s1, s2)
e1 * e2                                p1 + p2 + 1                            s1 + s2
e1 / e2                                p1 - s1 + s2 + max(6, s1 + p2 + 1)     max(6, s1 + p2 + 1)
e1 { UNION | EXCEPT | INTERSECT } e2   max(s1, s2) + max(p1-s1, p2-s2)        max(s1, s2)
e1 % e2                                min(p1-s1, p2 -s2) + max( s1,s2 )      max(s1, s2)

* The result precision and scale have an absolute maximum of 38. When a result 
  precision is greater than 38, the corresponding scale is reduced to prevent the 
  integral part of a result from being truncated.

And here's what we have for the variables in this table:

e1: @big_number, a DECIMAL(38, 0)
-> p1: 38
-> s1: 0

e2: 1, an INT
-> p2: 10
-> s2: 0

e1 / e2
-> Result precision: p1 - s1 + s2 + max(6, s1 + p2 + 1) = 38 + max(6, 11) = 49
-> Result scale:                    max(6, s1 + p2 + 1) =      max(6, 11) = 11

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 DECIMALs is 6.

Thus, we end up with the following results:

e1 / e2
-> Result precision: 49 -> reduced to 38
-> Result scale:     11 -> reduced to 6  

Note that 6 is the minimum possible scale it can be reduced to. 
It may be between 6 and 11 inclusive.

How this Explains the Arithmetic Overflow

Now the answer is obvious:

The output of our division gets cast to DECIMAL(38, 6), and DECIMAL(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):

DECLARE @big_number    DECIMAL(38,0) = '1' + REPLICATE(0, 37);
DECLARE @one_million   INT           = '1' + REPLICATE(0, 6);

PRINT @big_number / @one_million;

The result is:

10000000000000000000000000000000.000000

Note the 6 zeros after the decimal. We can confirm the result's data type is DECIMAL(38, 6) by using SQL_VARIANT_PROPERTY() as above:

DECLARE @big_number   DECIMAL(38,0) = '1' + REPLICATE(0, 37);
DECLARE @one_million  INT           = '1' + REPLICATE(0, 6);

SELECT
      SQL_VARIANT_PROPERTY(@big_number / @one_million, 'BaseType')  AS [BaseType]  -- decimal
    , SQL_VARIANT_PROPERTY(@big_number / @one_million, 'Precision') AS [Precision] -- 38
    , SQL_VARIANT_PROPERTY(@big_number / @one_million, 'Scale')     AS [Scale]     -- 6
;

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 to DECIMAL 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:

DECLARE @big_number     DECIMAL(38,0)  = '1' + REPLICATE(0, 37);
DECLARE @big_number_f   FLOAT          = CAST(@big_number AS FLOAT);

SELECT
      @big_number                           AS big_number      -- 10^37
    , @big_number_f                         AS big_number_f    -- 10^37
    , CAST(@big_number_f AS DECIMAL(38, 0)) AS big_number_f_d  -- 9999999999999999.5 * 10^21
;

And there you have it. Divide carefully, my children.

share|improve this answer
2  
2  
RE: "Cleaner Way". You might want to look at SQL_VARIANT_PROPERTY –  Martin Smith Jun 8 '12 at 20:04
    
@Martin - Could you provide an example or quick explanation of how I could use SQL_VARIANT_PROPERTY to perform divisions like the one discussed in the question? –  Nick Chammas Jun 11 '12 at 3:39
1  
There is an example here (as an alternative to creating a new table to determine the datatype) –  Martin Smith Jun 11 '12 at 5:49
    
@Martin - Ah yes, that's much neater! –  Nick Chammas Jun 11 '12 at 13:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.