tags:

views:

56

answers:

1

Hi,

I stumbled across this oddity when multiplying DECIMAL numbers on SQL Server 2005/2008. Can anyone explain the effect?

DECLARE @a DECIMAL(38,20)
DECLARE @b DECIMAL(38,20)
DECLARE @c DECIMAL(38,20)

SELECT  @a=1.0,
        @b=2345.123456789012345678,
        @c=23456789012345.999999999999999999

SELECT CASE WHEN @a*@b*@c = @c*@b*@a
       THEN 'Product is the same'
       ELSE 'Product differs'
       END
+2  A: 

It's due to precision representation and rounding errors.

The problem is due to

SELECT @a*@b   --(=2345.123457)

[Please search SO for multiple examples.]

Related: Sql Server Decimal(30,10) losing last 2 decimals

Mitch Wheat
Even though he is using a decimal which is storing an exact number? Based on the communicative property the product should always be the same. Unless perhaps they are storing the results of an intermediate calculation in a decimal type which we loose precision on, when we do the final multiply operation.
JoshBerke
The more I think about it, it does seem odd. You always try to rearaange expression to multiply large values by small ones, and to add/subtract like sized values due to loss of precision.
Mitch Wheat
@Josh, do you mean commutative property? That property is only valid in infinite-precision scenarios. Mitch is right - when you multiply two numbers with 18 decimal places, you get a product with 36 decimal places. A decimal(38, 20) isn't enough to hold that.
Aaronaught
@Aaronaught: That's what I guessed what is happening is that they are doing the multiplication based on the position of the variables in the expression. And that when @a*@b is multiplied they store the result in a decimal and then multiply by @c so when you change the order the precision would matter.
JoshBerke