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 1038 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 DECIMAL
s 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.
Best Answer
The column is actually stored as an int, and it means something different in datetime, so you'll need one extra step in your CONVERT:
To show why you can't do this directly to an int:
When it's an int, it's actually the number of days since 1900-01-01.