What am I missing?
-
Heh... yeah... When I started my post, your post wasn't there yet... I got interupted and came back to it. When I finally did post (forum hint: I always copy and paste my own reply because of the tendency of this forum to "time out"), there your's was... figured, that one more example using different numbers wouldn't hurt so I left it
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The trick we use to get the data the way we want is to do the calculations using the FLOAT but store the final ( or intermitent - if we need to have the numbers to be consistent for further calculations or comparison) results as decimal. If we need our numbers to be always rounded to x decimal points then we store them as DECIMALs and we do the comparison on the DECIMAL values not the FLOATs.
In the example Jeff posted about the division by 10 if you stored the FLOAT result into DECIMAL(10,1) you would get identical results.
The biggest disadventage with the FLOAT is that it uses its own logic to figure out the precision based on the value of the number you dealing with so you never really know what number is going to be presented to you.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
I am sorry but I tend not to agree with Sergiy on the precision. Where did you get the 14 digits precision of the decimals? Look at the following code and come to your own conclusions
DECLARE @One DECIMAL(38,30)
SET @One = 1.0
DECLARE @OneLittle DECIMAL(38,30)
SET @OneLittle = 1.0000000000000000000000000001
DECLARE @Result DECIMAL(38,30)
SET @Result = @One + @OneLittle
SELECT @Result
DECLARE @One FLOAT
SET @One = 1.0
DECLARE @OneLittle FLOAT
SET @OneLittle = 1.0000000000000000000000000001
DECLARE @Result FLOAT
SET @Result = @One + @OneLittle
SELECT @Result
The question is who in the real world deals with numbers like this.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
You don't have to use huge or very tiny numbers to get lossage with float/real.
They can be very tricky to deal with.
They are NEVER good for amounts that are meant to be exact.
I noticed a bit of confusion about precision and accuracy in the forum too.
Try running this code to get a feel for the difficulty.
Of course, if you follow the rules of rounding to the number of significant digits (one, in this case)it will of course be correct.
declare @one real
declare @tenth real
declare @index int
set @one=0
set @tenth=1.0/10.0
select @tenth
set @index=1
while @index <= 10
begin
set @one=@one+@tenth
set @index=@index+1
end
if (@one=1)
select @one ' and 1.0 are equal!'
else
select @one ,' and 1.0 are not equal!'
declare @rounded_one real
set @rounded_one = round(@one,1)
if (@rounded_one=1)
select @rounded_one ' and 1.0 are equal!'
else
select @rounded_one ,' and 1.0 are not equal!'
-
Just bad example.
Try this:
DECLARE @One DECIMAL(38,30)
SET @One = 1.0
DECLARE @Eleven DECIMAL(38,30)
SET @Eleven = 11.0000000000000000000000000000
DECLARE @Result DECIMAL(38,30)
SET @Result = @One / @Eleven
SELECT @Result
GO
DECLARE @One FLOAT
SET @One = 1.0
DECLARE @Eleven FLOAT
SET @Eleven = 11.0000000000000000000000000000
DECLARE @Result FLOAT
SET @Result = @One / @Eleven
SELECT @Result
Results not just diffeent, DECIMAL result is absolutely unacceptable.
> The question is who in the real world deals with numbers like this.
I did. When I was working on physics.
Try to calculate pressure of gas in a chamber based on mass of different atoms (about E-22), given number of each kind of atoms injected into the chamber (E20 .. E23), temperature (there is Boltzmann factor k = 1.38E-23 in the formulae) and volume of the chamber.
Are you sure DECIMAL numbers will help you there in any way?
_____________
Code for TallyGenerator -
Ah hah!!! THAT's why my checkbook is a mess! I used Kerchoff's law of voltage dividers as applied to a WheatStone Bridge instead of the good ol' Boltzmann factor k
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
It's good to know there is somebody around who still remembers old man Boltzmann.
_____________
Code for TallyGenerator -
I am still not completely convinced. There is no way you could get the 1.000000000000000000000001 into a float. There is not enough precision. The float is very good at dealing with very small and very large numbers but is not good enough in many cases for very precise numbers where the degree of precision is more then 17 (or so) digits. And those are the numbers you probably do not encounter in the real world I was talking about. The numbers you are talking about in your example are just very small numbers and DECIMAL can not deal with them but those numbers are not precise.
Your SQL example is very good and proves that for the division/multiplication the SQL server is casting the DECIMALS into real numbers (I don't even think they use the floats - because the precision would be greater) in order to perform the operations. So yes, if you want to have greater precision during multiplication/division you should always use float (and this is what we do, and I mentioned it in the post) but for storage, comparison and additions/subtractions with a specific precision you should use DECIMAL.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
PS.
By the way, I do understand this is mostly an academic discussion because the selection of specific data types for storage and calculations depends on the particular case you dealing with. I was just trying to make a point that precision is a bit more trickier then it appears.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
If you use FLOAT for calculations the result could not be more precise than FLOAT precision.
So, when you're storing FLOAT result as DECIMAL you fool yourself.
Yes, DECIMAL pleases your eyes and makes you think it's precise.
But it fails even in accounting when you need to share 1 dollar equally for 3 Cost Centers. You need to use some "rounding adjustments" to fix lack of precision in "precise" data types.
And if you perform another arithmetic operation on those numbers you probably lose another precise digit.
That's why money data type has 4 fractional digits despite the fact you never need more than 2 to display.
Last 2 digits not suppose to be considered reliable.
How academic is it?
_____________
Code for TallyGenerator -
msalomon,
try this:
declare @fOne float, @fThree float, @fOneThird float
declare @dOne decimal(12, 6), @dThree decimal(12, 6), @dOneThird decimal(12, 6)
SET @fOne = 1 SET @fThree = 3
SET @fOneThird = @fOne / @fThree
SELECT @fOneThird * @fThree
SET @dOne = 1 SET @dThree = 3
SET @dOneThird = @dOne / @dThree
SELECT @dOneThird * @dThree
Are these numbers from your real world?
What brings you precise result?
_____________
Code for TallyGenerator -
To make thing even worse for you lets try to perform second arithmetic operation:
declare @fOne float, @fThree float, @fFraction float, @fSeventeen float
declare @dOne decimal(12, 6), @dThree decimal(12, 6), @dFraction decimal(12, 6), @dSeventeen decimal(12, 6)
SET @fOne = 1 SET @fThree = 3 SET @fSeventeen = 17
SET @fFraction = @fOne / @fThree
SET @fOne = @fFraction * @fThree
SELECT @fOne
SET @fFraction = @fOne / @fSeventeen
SET @fOne = @fFraction * @fSeventeen
SELECT @fOne
SET @dOne = 1 SET @dThree = 3 SET @dSeventeen = 17
SET @dFraction = @dOne / @dThree
SET @dOne = @dFraction * @dThree
SELECT @dOne
SET @dFraction = @dOne / @dSeventeen
SET @dOne = @dFraction * @dSeventeen
SELECT @dOne
S, which @One is actually one?
_____________
Code for TallyGenerator -
Try this example:
DECLARE @dThird DECIMAL(12,6)
DECLARE @d99 DECIMAL(12,6)
DECLARE @fThird FLOAT
DECLARE @f99 FLOAT
SET @dThird = 0.33
SET @fThird = 0.33
SET @d99 = 3 * @dThird
SET @f99 = 3 * @fThird
SELECT @d99
SELECT @f99
Anyone of us can come up with any number of scenarios that will prove the other one is wrong. My point is that no one should claim blindly that one data type is always better or more accurate then the other because everything depends on the case you dealing with. And when I use the floats for calculations and store the results in decimals I am not fooling myself. I am making a conscious decision to keep this data this way to be consistent when I use those results later. If I have to present the data to the user using specific precision and I have to flag certain results based on some ranges I don't what to be in a position that we did not flag something because the number we used for flagging was a float and it was just outside the range but when we report the value to the user we show the value that is within the range.
We all should be aware of how the SQL server deals with certain operations and always understand what happens behind the scenes in order to make a most appropriate decision. I am in no way trying prove who is wrong and who is right because in this case I think we all are right if we do what we do understanding the consequences of our choices.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
What are you trying to prove?
0.33 is not 1/3.
And 3*.33 will return some result we have nothing to compare with.
Real test for precision must contain return to the point.
If you so some operations and perform reversal operations afterwards you suppose to receive initial value after all. As close as possible.
Because when you perfom some kind of distribution you still need to keep the same total value.
FLOAT does it always better than DECIMAL.
And don't mix calculations with presenting to the user.
FLOAT presents value in scientific notation. So, it must be used presentation only if you need to present data in scientific notation.
If your requirements specify another form of presentation you need to follow the requirements:
DECLARE @dThird DECIMAL(12,6)
DECLARE @d99 DECIMAL(12,6)
DECLARE @fThird FLOAT
DECLARE @f99 FLOAT
SET @dThird = 0.33
SET @fThird = 0.33
SET @d99 = 3 * @dThird
SET @f99 = 3 * @fThird
SELECT @d99
SELECT convert(DECIMAL(12,6), @f99)
_____________
Code for TallyGenerator -
How can you prove which result is right:
.990000
or
0.98999999999999999
?
Don't tell me about something you believe. Prove it.
The only scientific way to prove it is to try to get original value back.
If X / Y = Z then Z * Y must be X.
If it works than Z value you've got is the right one.
And don't let different forms of notations to fool you.
P.S. Bloody hell, am I teacher in school???
It's something I learnt before I went to university!
_____________
Code for TallyGenerator
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply