SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SET ROWCOUNT and table variable


SET ROWCOUNT and table variable

Author
Message
Rose Bud
Rose Bud
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1787 Visits: 1062
The main point of the QOD about testing for equality of floating point data is an excellent one. But, I admit I am more fascinated by this:

Hugo Kornelis (8/26/2010)
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.


I spent some time playing with Hugo's illustrations. Whoa. This seems like a fundamental thing of which I was entirely unaware. w00t
webrunner
webrunner
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7864 Visits: 4000
mccork (8/25/2010)
The answer was almost obvious from the "do not run it on production server" recommendation.

But, a good question for highlighting the pitfalls of "float".


That is what led me to guess correctly on this one, especially since that recommendation is literally one that goes without saying on SSC (no one should ever run QOTD code on production, even if they think that nothing can go wrong... Murphy's law and all that) - so I still need to read more about why this is. But I agree it is still a good question and leads to fruitful discussion about loops and floats.

Thanks,
webrunner

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Steve Eckhart
Steve Eckhart
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1220 Visits: 8664
Very good question. I discovered that whether or not 0.9 + 0.1 <> 1 is not consistent I guess depending on sequence. If you execute the following in SQL Server 2005, you get "Equal":

declare @f float
set @f = 0.9
set @f = @f + 0.1
if @f <> 1
print 'Not Equal'
else
print 'Equal'





Steve Eckhart
Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7491 Visits: 3395
Steve Eckhart (8/26/2010)
Very good question. I discovered that whether or not 0.9 + 0.1 <> 1 is not consistent I guess depending on sequence. If you execute the following in SQL Server 2005, you get "Equal":

declare @f float
set @f = 0.9
set @f = @f + 0.1
if @f <> 1
print 'Not Equal'
else
print 'Equal'


try this one:
declare @f float
set @f = 0
set @f = @f + 0.1 + 0.1 + 0.1 + 0.1 + 0.1 + 0.1 + 0.1 + 0.1 + 0.1 + 0.1
print 1 - @f

I run on tuttopodismo
weitzera
weitzera
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1362 Visits: 629
Pete,

No, by approximate, it is not meant that no finite numbers are representable, for example 1, .9, .1, and .005 are representable just fine. It is just an acknowledgment that certain numbers are not representable in base two, just as some numbers are not representable in base 10

In the example given, the problem is that .3 and .7 are not representable. A simple algorithm for converting a base 10 fraction to a base 2 fraction is as follows:
set number = .1 (remember, this is binary, .1 = 2^-1, or 1/(2^1) or .5 in decimal)
set exp = -1
while number <> target
if number > target
set number = number - 2^exp
end if
set exp = exp - 1
set number = number + 2^exp
end while


If you follow this for .3 you get:
.01001100110011001100110011... ad nauseum

Compare this to the process of converting .1 from base 3 into base 10 (.1 in base 3 is the ratio 1/3)

A common question when this explanation is given is: 3 can be represented in base 2, and -1 can be represented, so why doesn't the floating point data type represent the value as "3 * 10^-1" The answer as I understand it is that floats are highly optimized for storage space, so for the same number of bits, you would get less range of representable numbers from a float specified this way. Feel free to correct me on this if there were other considerations I'm forgetting.



Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'

john.arnott
john.arnott
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3590 Visits: 3059
Here's another little script to demonstrate the effect of using float. Although the local variable looks pretty good out of a simple "print @i", converting the value to decimal bears out the internal inaccuracy.
declare @i float
set @i = .1
while @i <= 10.0
begin
print @i
print convert(decimal(38,36),@i)
set @i = @i + .1
End


The first few rows returned are here:
0.1
0.100000000000000000000000000000000000
0.2
0.200000000000000000000000000000000000
0.3
0.300000000000000060000000000000000000
0.4
0.400000000000000000000000000000000000
0.5
0.500000000000000000000000000000000000
0.6
0.600000000000000000000000000000000000
0.7
0.700000000000000000000000000000000000
0.8
0.799999999999999880000000000000000000
0.9
0.899999999999999880000000000000000000
1
0.999999999999999880000000000000000000

and the last few are here:

9.6
9.599999999999981900000000000000000000
9.7
9.699999999999981500000000000000000000
9.8
9.799999999999981200000000000000000000
9.9
9.899999999999980800000000000000000000
10
9.999999999999980400000000000000000000

It's now obvious that 9.9999999999999804 is not equal to 10.
Scott Coleman
Scott Coleman
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7248 Visits: 1531
weitzera (8/26/2010)
for example 1, .9, .1, and .005 are representable just fine


The mantissa of a FLOAT is a 53-bit binary fraction, and it can exactly represent only rational numbers whose denominator is a power of 2. This means that 0.1 can't be represented exactly, the value stored is actually closer to 0.1000000000000000055511. Neither can 0.9 or 0.005. Any integer value under 15 digits long can be stored exactly, as can binary fractions like 0.5, 0.25, 0.125, etc.

Floating-point calculations are done in the CPU with 80-bit precision and the result is rounded to 53 bits, which in many cases works very well and the approximation errors cancel out. 10 x 0.1 results in exactly 1.0, for example. But repeatedly adding 0.1 means the same tiny error is added at each step and it eventually becomes noticable as shown in the examples posted above.



Gopi S
Gopi S
Old Hand
Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)

Group: General Forum Members
Points: 301 Visits: 325
Hi,
But another sample query as shown below gives expected result which contradicts from while loop.

DECLARE @i float
set @i = .9
set @i = @i + .1

-- Query 1
if @i <> 1
print 'True'
else
print 'False'

Answer: 'False'

-- Query 2
if @i = 1
print 'True'
else
print 'False'

Answer: 'True'

Can anyone pls explain on difference in this two. (ie while loop resulted is true but if condition in query 1 returned 'False'
Pete Cox
Pete Cox
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 277
weitzera and scott,

Thank you for the explanations, I have a much better understanding of "float" approximation issues now
mm_sharma82
mm_sharma82
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 23
Hello All,
I'm not agree with explaination given.
"Floating point data is approximate (http://msdn.microsoft.com/en-us/library/ms173773.aspx). So an increment of 0.1 will never be equal to 1 and the exit condition @i <> 1 always is true (infinite loop).

The script contains two bad mistakes: floating comparison and a WHILE condition that easily falls into infinite loop. "

Following script is also increasing the variable @i, but it iterate loop once.

DECLARE @i float,@rc int
set @i = 0

while @i <> 1
begin
declare @a table(a int)
set @i = @i + 0.9
insert into @a
select 1
set @i = @i + 0.1
end

-- LAST SELECT
SELECT * FROM @a
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search