**Carlo Romagnano (8/30/2010)**

**nisan.al (8/29/2010)**

I can't say i completely understand this issue.

The while statement dont not stop because @i will never be equal to 1

So how come the following select return 1 ?

DECLARE @i float

set @i = 0

set @i = @i + 0.2

set @i = @i + 0.2

set @i = @i + 0.2

set @i = @i + 0.2

set @i = @i + 0.2

SELECT 1

WHERE @i=1As said above, the increment of 0.2 (precise) is different from 0.1 (approximate)

No, that is not the reason. Neither 0.1, nor 0.2 can be represented precisely in binary floating point.

The binary equivalent of 0.2 is 0.000110001100011000110......

The binary equivalent of 0.1 is 0.000011000110001100011......

The real reason is that, with all rounding errors, you only see the effect if it stacks sufficiently. Because the calculation is done with some extra bits, most rounding errors remain invisible, especially becuase in random operations the rounding tend to even out each other. Repeating the same operation means repeating the same rounding error - and that will become visible when repeated often enough.

DECLARE @i float;

set @i = 0;

set @i = @i + 0.2;

set @i = @i + 0.2;

set @i = @i + 0.2;

set @i = @i + 0.2;

set @i = @i + 0.2;

SELECT 1

WHERE @i=1;

set @i = @i + 0.2;

set @i = @i + 0.2;

set @i = @i + 0.2;

set @i = @i + 0.2;

set @i = @i + 0.2;

SELECT 2

WHERE @i=2;

The first SELECT returns a value (as indicated by nisan.al), because the sum of the five rounding errors is so small that it has not yet reached the significant bits for the comparison. The second SELECT does not return a value, so obviously the sum of ten rounding errors does bring it into the significant bits.

A good way to visualize this, is to check the corresponding rounding errors when using decimal notation. Calculate 1/with one decimal place (0.3). Add it three times (0.9). Compare it to the integer 1 (0.9 rounds to 1, so conclusion is: 1 = the sum of three (1/3)).

Now add the same rounded 1/3 fifteen times more (5.4). Compare to the integer 6 (5.4 rounds to 5; not equal, so now the conclusion is that 6 <> the sum of eighteen (1/3)).

The flloating point calculations obviously use lots more siginificant digits, but so do the comparisons; the problem remains the same.

Hugo Kornelis, SQL Server MVP

Visit my SQL Server blog:

http://sqlblog.com/blogs/hugo_kornelis