Why does 10^37 / 1 throw an arithmetic overflow error?

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?

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

StackExchange Database Administrators Q#18997, answer score: 18