Why does “SELECT POWER(10.0, 38.0);” throw an arithmetic overflow error?

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)?
-
Isn't the largest value simply 38 9s? e.g. this works fine for me ... why do you have to calculate it? `DECLARE @f FLOAT = 99999999999999999999999999999999999999;` – Aaron Bertrand Oct 17 '11 at 21:22
While `FLOAT` may be able to store more than 38 9s, are you really going to create an `IDENTITY` column that ends up doing this in real life? Do you know how many `INSERT`s you will have to perform, per second, to hit this upper bound? – Aaron Bertrand Oct 17 '11 at 21:39
`SELECT POWER(CAST(10 as float), 38.0);` works for me – Martin Smith Oct 17 '11 at 21:41
@Nick - Actually just occurred to me that you could use `1e38` directly. – Martin Smith Oct 17 '11 at 22:09
POWER(10.0, 38.0) would require 39 digits to store. The largest possible decimal(38) is POWER(10.0, 38.0) - 1 – A-K May 2 '12 at 18:33

It seems that despite the implication in BOL that the left hand operand will be implicitly cast to float that this is not the case. The output of `POWER()` is cast to the type of the left hand operand, which is `DECIMAL` if you use `10.0`. Using an explicit `float` works fine.

``````SELECT POWER(1e1, 38);
SELECT POWER(CAST(10 as float), 38.0);
``````
-
Just ran into this: Why does `SELECT POWER(CAST(10 AS DECIMAL(38,0)), 37)` return an incorrect result? It's easy to verify that `DECIMAL(38,0)` is able to store the result of 10^37: `SELECT CAST('1' + REPLICATE(0, 37) AS DECIMAL(38, 0));` – Nick Chammas Jun 7 '12 at 21:32
@NickChammas - Not sure. `DECLARE @Ten DECIMAL(38,0) = 10; SELECT @Ten*@Ten*@Ten*@Ten*@Ten*@Ten*@Ten*@Ten*@Ten*@Ten*@Ten*@Ten*@Ten*@Ten*@Ten*@Ten*‌​@Ten*@Ten*@Ten*@Ten*@Ten*@Ten*@Ten*@Ten*@Ten*@Ten*@Ten*@Ten*@Ten*@Ten*@Ten*@Ten*@‌​Ten*@Ten*@Ten*@Ten*@Ten` works fine. – Martin Smith Jun 8 '12 at 7:06
@NickChammas - Maybe it does cast the LHS to `float` before doing the calculation then casts the result back `SELECT CAST(POWER(CAST(10 as float), 37) AS DECIMAL(38,0))` – Martin Smith Jun 8 '12 at 9:39
Ahh, I thought about that and then dismissed it because `SELECT POWER(CAST(10 AS FLOAT), 37)` returns the correct result. I didn't consider that converting 10^37 from `FLOAT` to `DECIMAL` would be an issue, but it is: `SELECT CAST(1e37 AS DECIMAL(38,0))` – Nick Chammas Jun 8 '12 at 14:42

Instead of meddling with Martin's answer any further, I'll add the rest of my findings regarding `POWER()` here.

Preamble

First, I present to you exhibit A, the MSDN documentation for `POWER()`:

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.

Return Types

Same as `float_expression`.

You may conclude from reading that last line that `POWER()`'s return type is `FLOAT`, but read again. `float_expression` is "of type float or of a type that can be implicitly converted to float". So, despite its name, `float_expression` may actually be a `FLOAT`, a `DECIMAL`, or an `INT`. Since the output of `POWER()` is the same as that of `float_expression`, it too may also be one of those types.

So we have a scalar function with return types that depend on the input. Could it be?

Observations

I present to you exhibit B, a test demonstrating that `POWER()` casts its output to different data types depending on its input. Please read this script with a faux Jamaican accent for maximum derived benefit.

``````SELECT
POWER(10, 3)             AS int_man
, POWER(1000000000000, 3)  AS numeric0_man     -- one trillion
, POWER(10.0, 3)           AS numeric1_man
, POWER(10.12305, 3)       AS numeric5_man
, POWER(1e1, 3)            AS float_man
INTO power_test_man;

EXECUTE sp_help power_test_man;

DROP TABLE power_test_man;
``````

The relevant results are:

``````Column_name    Type      Length    Prec     Scale
-------------------------------------------------
int_man        int       4         10       0
numeric0_man   numeric   17        38       0
numeric1_man   numeric   17        38       1
numeric5_man   numeric   17        38       5
float_man      float     8         53       NULL
``````

What appears to be happening is that `POWER()` casts `float_expression` into the smallest type that fits it, not including `BIGINT`.

Therefore, `SELECT POWER(10.0, 38);` fails with an overflow error because `10.0` gets cast to `NUMERIC(38, 1)` which isn't big enough to hold the result of 1038. That's because 1038 expands to take 39 digits before the decimal, whereas `NUMERIC(38, 1)` can store 37 digits before the decimal plus one after it. Therefore, the maximum value `NUMERIC(38, 1)` can hold is 1037 - 0.1.

Armed with this understanding I can concoct another overflow failure as follows.

``````SELECT POWER(1000000000, 3);    -- one billion
``````

One billion (as opposed to the one trillion from the first example, which is cast to `NUMERIC(38, 0)`) is just small enough to fit in an `INT`. One billion raised to the third power, however, is too big for `INT`, hence the overflow error.

Several other functions exhibit similar behavior, where their output type is dependent on their input:

• Mathematical functions: `POWER()`, `CEILING()`, `FLOOR()`, `RADIANS()`, `DEGREES()`, and `ABS()`
• System functions and expressions: `NULLIF()`, `ISNULL()`, `COALESCE()`, `IIF()`, `CHOOSE()`, and `CASE` expressions
• Arithmetic operators: Both `SELECT 2 * @MAX_INT;` and `SELECT @MAX_SMALLINT + @MAX_SMALLINT;`, for example, result in arithmetic overflows when the variables are of the named data type.

Conclusion

In this particular case, the solution is to use `SELECT POWER(1e1, precision)...`. This will work for all possible precisions since `1e1` gets cast to `FLOAT`, which can hold ridiculously large numbers.

Since these functions are so commonplace, it's important to understand that your results may be rounded or may cause overflow errors due to their behavior. If you expect or rely on a specific data type for your output, explicitly cast the relevant input as necessary.

So kids, now that you know this, you may go forth and prosper.

Yeah man.

-
+1 but The maximum value NUMERIC(38, 1) can hold is not 10^37, it is 10^38-1 – A-K May 2 '12 at 20:11
@Alex - `DECLARE @BigNumber NUMERIC(38,1) = 10; SET @BigNumber = POWER(@BigNumber, 37) * 10.0 - 1.0` – Nick Chammas May 2 '12 at 21:11
@Alex - Also: `DECLARE @BigNumber NUMERIC(38,1); DECLARE @Float FLOAT = POWER(1e1, 38) - 1; SET @BigNumber = @Float;` Can you get a `NUMERIC(38,1)` to hold 10^38-1? – Nick Chammas May 2 '12 at 21:24
@NickChammas yes my comment was not clear at all. I meant that the maximium number it can hold is a number consisting of 37 digits 9: `DECLARE @BigNumber NUMERIC(38,1) = 10; SET @BigNumber = CAST(REPLICATE('9', 37) AS NUMERIC(38,1)) SELECT @BigNumber – A-K May 3 '12 at 19:27
@Alex - Ah, I see. In that case, it's not 10^38 - 1 either. The max value `NUMERIC(38,1)` can hold is 10^37 - 0.1, which is 37 9s before the decimal and 1 9 after the decimal. (Note that expanding 10^n requires n+1 digits, and 10^n - 0.1 still takes n+1 digits, n of them before the decimal and 1 after the decimal.) – Nick Chammas May 4 '12 at 20:30