August 25, 2010 at 8:40 pm
Comments posted to this topic are about the item SET ROWCOUNT and table variable
August 25, 2010 at 8:51 pm
This is an excellent question, thank you. I answered it correctly because for years I cannot find the answer to the following question: why would anyone ever consider casually using float data type? I understand that there might be some specific scenarios where the inaccuracy of calculations are not important and the range of the numbers is huge, but using float for no good reason is astonishingly bad not to say plain silly. The query in Qotd is one fine example demonstrating this point.
Oleg
August 25, 2010 at 9:57 pm
Thanks for the question, I had to puzzle it out for a while, but got it right.
August 25, 2010 at 10:47 pm
for testing folating point the same can be written as
DECLARE @i float,@rc int
set @i = 0
while @i <> 1
begin
set @i = @i + 0.1
end
select @i
then the loop never executes.
I observer some intrested behavior of floating point
DECLARE @i float,@rc int
set @i = 0
while @i <> 1
begin
set @i = 0.8999 + 0.1
end
select @i
the above will never executes. we use 0.1 instead of 0.8999 other wise we can use 0.2,0.3,0.4,0.5,0.6,0.7,0.8.but when i added 0.9 it executes
and show 1 as answer.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
August 25, 2010 at 10:54 pm
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".
Cheers,
- Mark
August 25, 2010 at 11:42 pm
This was removed by the editor as SPAM
August 26, 2010 at 12:32 am
Thanks for a great question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 26, 2010 at 1:03 am
Great question. I got it wrong though. 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?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 26, 2010 at 1:03 am
Very interesting question and great explanations. Thanks
August 26, 2010 at 1:33 am
isn't it possible to use approximation if one is not specific of the exact value of the approximation, like "less than" and "greater than" ?
August 26, 2010 at 1:52 am
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.
August 26, 2010 at 2:15 am
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)
August 26, 2010 at 2:18 am
stewartc-708166 (8/25/2010)
The only possible use for the float datatype that I have found is in the calculations used in astro-physics, where the distance between stars and galaxies is an approximation, at best
Add physics, chemistry and all other parts of science that deal with measured data. All measurements are always approximations.
When you have to deal with extremely large values (as in astro-physics) or extremely small values (like atomic weight or distance in chemistry), floating point data is the only reasonable choice. For other data, both floating point and fixed point canbe used though floating point often is better).
August 26, 2010 at 2:21 am
Last comment, and then I'll shut up (for now)
Open Minded (8/26/2010)
isn't it possible to use approximation if one is not specific of the exact value of the approximation, like "less than" and "greater than" ?
Yes. A test of "IF @float > 1.0" is okay.
And if you really have to test for equality with floating point data, you have to consider how many of the decimals are relevant and then round: "IF ROUND(@float, 3) = 1.000" should work. If you change the WHILE in this question to "while round(@1,3) <> 1", the code finishes and returns a single value.
August 26, 2010 at 2:34 am
Floats can be useful when the same column needs to be able to hold either large values (lots of numbers before the decimal point) or very small values (lots of numbers after it).
There are of course loads of pitfalls to be aware of, but in general, so longer as you always round the number to an appropriate number of decimal places then things will work.
eg in this qotd
while @i <> 1
could be something like
while round(@i,8) <> 1
However, they are definitely better avoided if there's a reasonable alternative!
Viewing 15 posts - 1 through 15 (of 54 total)
You must be logged in to reply to this topic. Login to reply