SQL Server – SELECT POWER(10.0, 38.0) Arithmetic Overflow Error

datatypessql serversql-server-2008t-sqltype conversion

I'm updating my IDENTITY overflow check script to account for DECIMAL and NUMERIC IDENTITY columns.

As part of the check I compute the size of the data type's range for every IDENTITY column; I use that to calculate what percentage of that range has been exhausted. For DECIMAL and NUMERIC the size of that range is 2 * 10^p - 2 where p is the precision.

I created a bunch of test tables with DECIMAL and NUMERIC IDENTITY columns and attempted to calculate their ranges as follows:

SELECT POWER(10.0, precision)
FROM sys.columns
WHERE 
       is_identity = 1
   AND type_is_decimal_or_numeric
;

This threw the following error:

Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting float to data type numeric.

I narrowed it down to the IDENTITY columns of type DECIMAL(38, 0) (i.e. with the maximum precision), so I then tried the POWER() calculation directly on that value.

All of the following queries

SELECT POWER(10.0, 38.0);
SELECT CONVERT(FLOAT, (POWER(10.0, 38.0)));
SELECT CAST(POWER(10.0, 38.0) AS FLOAT);

also resulted in the same error.

Why does SQL Server try to convert the output of POWER(), which is of type FLOAT, to NUMERIC (especially when FLOAT has a higher precedence)?
How can I dynamically calculate the range of a DECIMAL or NUMERIC column for all possible precisions (including p = 38, of course)?

Best Answer

From the POWER documentation:

Syntax

POWER ( float_expression , y )

Arguments

float_expression
Is an expression of type float or of a type that can be implicitly converted to float.

y
Is the power to which to raise float_expression. y can be an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Return Types

Returns the same type as submitted in float_expression. For example, if a decimal(2,0) is submitted as float_expression, the result returned is decimal(2,0).

The first input is implicitly cast to float if necessary.

The internal calculation is performed using float arithmetic by the standard C Runtime Library (CRT) function pow.

The float output from pow is then cast back to the type of the left hand operand (implied to be numeric(3,1) when you use the literal value 10.0).

Using an explicit float works fine in your case:

SELECT POWER(1e1, 38);
SELECT POWER(CAST(10 as float), 38.0);

An exact result for 10³⁸ cannot be stored in a SQL Server decimal/numeric because it would require 39 digits of precision (1 followed by 38 zeros). The maximum precision is 38.

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 10³⁷.

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 10³⁷ 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.

Sql-server – Why doesn’t `1` fit into a decimal(4, 4) column

The Precision specifies the total number of digits that can be stored, the Scale specifies, how many of those digits live behind the decimal point.

A DECIMAL(4,4) therefore has four digits and all are behind the decimal point. That means you can store values form -0.9999 to 0.9999. You cannot store -1 or 1 as they have a digit before the decimal point.

SQL Fiddle

Query 1:

SELECT CAST(-0.9999 as DECIMAL(4,4)),CAST(0.123456 as DECIMAL(4,4)),CAST(0.9999 as DECIMAL(4,4))

Results:

| COLUMN_0 | COLUMN_1 | COLUMN_2 |
----------------------------------
|  -0.9999 |   0.1235 |   0.9999 |

To store 1 you have to add an additional digit as in DECIMAL(5,4). That allows you to store values from -9.9999 to 9.9999:

SQL Fiddle

Query 2:

SELECT CAST(-9.9999 as DECIMAL(5,4)),CAST(1 as DECIMAL(5,4)),CAST(9.9999 as DECIMAL(5,4))

Results:

| COLUMN_0 | COLUMN_1 | COLUMN_2 |
----------------------------------
|  -9.9999 |        1 |   9.9999 |

Best Answer

Syntax

Arguments

Return Types

Related Solutions

Sql-server – Why does 10^37 / 1 throw an arithmetic overflow error

Understanding Precision and Scale in the context of Arithmetic Operations

Result Types

How this Explains the Arithmetic Overflow

A Dangerous Workaround

Sql-server – Why doesn’t `1` fit into a decimal(4, 4) column

Related Question