Multiplication and Division with Numerics
It can be surprising to see certain results when doing numeric arithmetic:
declare @num1 numeric(38,10)
declare @num2 numeric(38,10)
set @num1 = .0000006
set @num2 = 1.0
select cast( @num1 * @num2 as numeric(38,10))
Yields: .0000010000
Instead of: .0000006000
Why?
Well, Books Online (see Precision, Scale, and Length) dictates the following rule for numeric arithmetics:
|
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) |
In this case, the multiplication result precision and scale are computed as follows:
Precision = P1 + P2 + 1 = 38 + 38 + 1 = 77
Scale = S1 + S2 = 10 + 10 = 20
So conceptually, the result should be numeric(77, 20), which isn't allowed. This is where the fine prints come in:
* 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.
Books Online currently doesn't go into the specifics on how such truncation is performed.
Since the precision is higher than 38, we try to avoid truncating the integral part of the value by reducing the scale (thus truncating the decimal part of the value instead). How much scale should be sacrificed? There is no right answer. If we preserve too much, and the result of the multiplication of large numbers will be way off. If we preserve too little, multiplication of small numbers becomes an issue.
In SQL Server 2005 RTM (and previous versions), we decided preserve a minimum scale of 6 in both multiplication and division. So our numeric(77,20) is truncated as numeric(38,6), and then is then casted as numeric(38,10). However at this point it is too late, and some data has been lost. This explains the result you would see in the previous expression.
Because of this, it is important to try to minimally quantify the precision and scale of the operands involved in multiplication and division. In this case:
declare @num1 numeric(18,10)
declare @num2 numeric(18,10)
set @num1 = .0000006
set @num2 = 1.0
select cast( @num1 * @num2 as numeric(38,10))
The resulting type would be numeric(37,20). Since this type's precision and scale does not exceed our current limits, no implicit truncation is performed. We then cast the result as numeric(38,10), which doesn't cause data loss in our case.
If you cannot accurately type the values involved in multiplication and division, for example if they are procedure parameters that are called with wildly different values, you may want to look into approximate numeric types (float, real), or perhaps define your own very high capacity user defined exact numeric data type using the CLR.
I hope this was useful. Let us know if there are particular topics you'd like to hear about!
-Mat