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.

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

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

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