26 October 2007 16:07 Alex_Kuznetsov

When SUM of Six Floats Depends on Order of Adding

Following the discussion started by Hugo Kornelis and Old Grumpy DBA, an example of inexact calculations with float datatype:

declare @big float, @small float, @sum1 float, @sum2 float, @sum3 float, @i INT
SELECT @big = 12345678901234.50, @small = 0.01, @i = 0
SELECT @sum1 = @big, @sum2 = 0, @sum3 = 0
WHILE @i < 5 BEGIN
  SELECT @sum1 = @sum1 + @small, @sum2 = @sum2 + @small, @sum3 = @sum3 + @small
  SET @i = @i + 1
END
SELECT @sum2 = @sum2 + @big
SELECT @sum1, @sum2, @sum3

---------------------- ---------------------- ----------------------
12345678901234.5       12345678901234.6       0.05

(1 row(s) affected)

This is why you cannot have sums of floats in indexed views - they are not deterministic, they may depend on order of adding.

Comments

# re: When SUM of Six Floats Depends on Order of Adding

26 October 2007 22:45 by Hugo Kornelis

Hi Alex,

ANY datatype that has rounding issues, can have different results if the order of calculation is changed. That is clearly visible for float, as opposed to decimal, when doing additions with numbers with a finite number of decimal places. But when doing multiplications, the rounding issues of decimal are much easier to spot than the rounding problems of float:

DECLARE @f1 float, @f2 float, @f3 float;

DECLARE @n1 numeric(38,10), @n2 numeric(38,10), @n3 numeric(38,10);

SET @f1 = 123456789.012345;

SET @n1 = 123456789.012345;

SET @f2 = 0.00001;

SET @n2 = 0.00001;

SET @f3 = 10000.0;

SET @n3 = 10000.0;

SELECT (@f1 * @f2) * @f3, (@f1 * @f3) * @f2;

SELECT (@n1 * @n2) * @n3, (@n1 * @n3) * @n2;

# Calculating cumulative product from previous row (without using cursors) - Page 2 | keyongtech

18 January 2009 17:21 by Calculating cumulative product from previous row (without using cursors) - Page 2 | keyongtech

Pingback from  Calculating cumulative product from previous row (without using cursors) - Page 2 | keyongtech