Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Wang
Starting Member
48 Posts |
 Posted - 2006-03-01 : 09:48:42
|
Hi, posted elsewhere, but no response yet that really helps me. Hope you guys can!Basically large precision decimals are losing scale when multiplied together, unless the scale is almost equally large. Examples below. SQL2k Sp3.declare @a decimal(38,15), @B decimal(38,15), @c decimal(38,15)select @a = 1.455232546573737, @B = 3.145614454544622 select @a, @B , @a * @bselect @c = @a * @bselect @Cgodeclare @a decimal(18,15), @B decimal(18,15), @c decimal(18,15)select @a = 1.455232546573737 , @B = 3.145614454544622select @a, @B , @a * @bselect @c = @a * @bselect @Cgodeclare @a decimal(38,33), @B decimal(38,33), @c decimal(38,33)select @a = 1.455232546573737, @B = 3.145614454544622select @a, @B , @a * @bselect @c = @a * @bselect @CThe first gives 4.577601000000000The second gives 4.577600533226127The third gives 4.577600533226126943974679792000000Both the 2nd and 2rd give sufficiently accurate answers, but lose the capacity for large numbers. Now although the need for such accuracy is perhaps debatable, I really want to understand how these 'exact' datatypes truly behave. BOL: quote:
The precision and scale of the numeric data types besides decimal are fixed. If an arithmetic operator has two expressions of the same type, then the result has the same data type with the precision and scale defined for that type. If an operator has two expressions with different numeric data types, then the rules of data type precedence define the data type of the result. The result has the precision and scale defined for its data type.This table defines how the precision and scale of the result are calculated when the result of an operation is of type decimal. The result is decimal when: Both expressions are decimal.One expression is decimal and the other is a data type with a lower precedence than decimal. The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. The precision and scale for any expression that is not decimal is the precision and scale defined for the data type of the expression.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) 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) * 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.
Now it appears to me that it is this bit at the bottom that applies here, but I don't really get it. Please help! |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-03-01 : 12:05:30
|
i don't really understand what don't you really get??when you do a*b the scales get sumed up but are <= 38 when you did select @c which had scale 15 the result got rounded to that scale.Go with the flow & have fun! Else fight the flow  |
 |
|
|
Wang
Starting Member
48 Posts |
Posted - 2006-03-02 : 05:33:38
|
| Let me summarise:The first gives 4.577601000000000 (6dps - multiplying 2 (38,15))The second gives 4.577600533226127 (15dps - multiplying 2 (18,15))The third gives 4.577600533226126943974679792000000 (27dps - multiplying 2 (38,33))All using the SAME 15dp numbers. |
|
|
|
|
|
|
|
|
Decimal Rounding