# SET ROWCOUNT and table variable

• Thanks, Paul. That was the part that I missed that caused the misunderstanding on my part. I got in my head that we were starting @i at 0.9, but we're not. We're starting @i at 0 and incrementing by 0.1 which causes us to "miss" 1.0.

Steve Eckhart

• mccork (8/25/2010)

The answer was almost obvious from the "do not run it on production server" recommendation.

<<snip>>

Agreed (and frankly, I wouldn't have chosen the right answer otherwise :-))!

• 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=1

• 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=1

As said above, the increment of 0.2 (precise) is different from 0.1 (approximate)

• 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=1

As 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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

• good question. Thanks!

• Hugo Kornelis (8/26/2010)

da-zero (8/26/2010)

I do not really understand how the scope of the type variable works. It seems you can define it over and over again, without an error being thrown?

and

martin.whitton (8/26/2010)

It's interesting that a variable can be declared multiple times if it's within a "while" loop (provided that it's only declared once in each iteration).

When I first read the question I thought that declaring @a within the loop would cause an error on the second iteration.

The variable is actually declared once. Declarations are not relly executable statements. They are processed when a batch is parsed, not when it it executed. The declaration has to be positioned before the use in "left to right / top to bottom" order, not in execution order. That is why this works, even though the code path that containst the declaration is never executed.

`IF 1 = 2`

`BEGIN;`

` PRINT 'Skip this';`

` DECLARE @a int;`

`END;`

`ELSE`

`BEGIN;`

` SET @a = 1;`

`END;`

`SELECT @a;`

And this works as well, even though the part where the declaration sits is executed AFTER the assignment:

` GOTO Label2;`

`Label1:`

` DECLARE @b-2 int;`

` SELECT @b-2;`

` GOTO Label3;`

`Label2:`

` SET @b-2 = 3;`

` GOTO Label1;`

`Label3:`

`go`

(Note that I do not endorse using GOTO in T-SQL code, nor deliberately writing spaghetti code for any other purposes than illustrating the difference between order of parsing and order of execution)

Kind of new for me - thanks - great explanation and examples

• I'll parrot the thanks. Good question and good examples which I definitely needed.

-Dan B

• Should have listened to my gut instinct on reading "do not run it on production server" 🙂

• Good question and the last name will not be reached.

Viewing 10 posts - 46 through 54 (of 54 total)