SET ROWCOUNT and table variable

Question

SET ROWCOUNT and table variable

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

You must be logged in to reply to this topic. Login to reply

Steve Eckhart SSCommitted Points: 1880 More actions · Answer 1

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

Michael Poppers SSCrazy Points: 2151 More actions · Answer 2

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 :-))!

nisan.al SSC Enthusiast Points: 190 More actions · Answer 3

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

SELECT 1

WHERE @i=1

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 4

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)

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 5

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;

SELECT 1

WHERE @i=1;

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/

Kangana Beri SSCrazy Points: 2184 More actions · Answer 6

Kangana Beri

SSCrazy

Points: 2184

September 1, 2010 at 5:45 pm

#1215351

good question. Thanks!

gchornenkyy Say Hey Kid Points: 687 More actions · Answer 7

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

skrilla99 SSCertifiable Points: 5795 More actions · Answer 8

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

-Dan B

skrilla99 SSCertifiable Points: 5795 More actions · Answer 9

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

zymos SSCommitted Points: 1960 More actions · Answer 10

Good question and the last name will not be reached.