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

Friday, October 26, 2007 10:45 PM 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

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

Tuesday, December 27, 2016 10:12 AM by buy ansomone online