Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»»

SET ROWCOUNT and table variable Expand / Collapse
Author
Message
Posted Thursday, August 26, 2010 8:01 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 1:04 PM
Points: 1,313, Visits: 1,055
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.
Post #975659
Posted Thursday, August 26, 2010 8:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:50 AM
Points: 2,328, Visits: 2,658
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


-------------------
"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
Post #975681
Posted Thursday, August 26, 2010 8:37 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 7:18 AM
Points: 989, Visits: 8,655
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
Post #975702
Posted Thursday, August 26, 2010 8:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 6:24 AM
Points: 2,451, Visits: 2,342
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
Post #975708
Posted Thursday, August 26, 2010 10:42 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, June 13, 2014 10:04 AM
Points: 880, Visits: 605
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.'
Post #975785
Posted Thursday, August 26, 2010 2:02 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 6:26 AM
Points: 1,521, Visits: 3,036
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.
Post #975927
Posted Thursday, August 26, 2010 3:11 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:13 PM
Points: 2,837, Visits: 1,138
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.



Post #975964
Posted Friday, August 27, 2010 3:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:01 PM
Points: 157, Visits: 286
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'
Post #976249
Posted Friday, August 27, 2010 3:53 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 14, 2013 1:05 AM
Points: 96, Visits: 277
weitzera and scott,

Thank you for the explanations, I have a much better understanding of "float" approximation issues now

Post #976252
Posted Friday, August 27, 2010 3:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 25, 2013 10:32 AM
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
Post #976255
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse