Instead of meddling with Martin's answer any further, I'll add the rest of my findings regarding
Hold on to your knickers.
First, I present to you exhibit A, the MSDN documentation for
POWER ( float_expression , y )
Is an expression of type float or of a type that can be implicitly converted to float.
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
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?
I present to you exhibit B, a test demonstrating that
POWER() casts its output to different data types depending on its input.
POWER(10, 3) AS int
, POWER(1000000000000, 3) AS numeric0 -- one trillion
, POWER(10.0, 3) AS numeric1
, POWER(10.12305, 3) AS numeric5
, POWER(1e1, 3) AS float
EXECUTE sp_help power_test;
DROP TABLE power_test;
The relevant results are:
Column_name Type Length Prec Scale
int int 4 10 0
numeric0 numeric 17 38 0
numeric1 numeric 17 38 1
numeric5 numeric 17 38 5
float float 8 53 NULL
What appears to be happening is that
float_expression into the smallest type that fits it, not including
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:
- System functions and 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.
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.