February 15, 2008 at 8:03 am
Jeff Moden (2/15/2008)
All the code does is multiply the DECIMAL and FLOAT versions of the number .3333 times an INTEGER. Notice in column 3 how early (3rd row) the DECIMAL answer departs from the correct answer...
Again - we're going to have a difference of opinion here. .33 is NOT 1/3 - never has been, never will be. It's "wrong" only when you have no understanding of rounding. In other words, the only error there is in the "eye of the beholder".
(when have I said this before....could it be last time I "caught you posting this"? hehe) Decimals and floats will never be the same, so drawing a parallel between the two is quite honestly the first flaw.
And I understand that you're trying to open people's eyes to this difference, but stating that decimal is "incorrect" when it's not is a problem.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 15, 2008 at 8:38 am
Caught me? Heh... I jumped right out in front of ya 😀
What you say is similar to what I used to say and think.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 15, 2008 at 8:41 am
GSquared (2/15/2008)
Even in that case, using float instead of decimal resulted in a measureable speed increase. (I have to calculate distances between sets of up to 4-million records and sets of up to about 1,000 records, as a cross-join. Every millisecond per record counts at that point.) Just have to be aware that the rounding issue can bite you, and make sure to correct for it.
Yeah, then there's that... I forgot to mention the speed advantage that the inherent binary calculations of FLOAT have over the BCD calculations of DECIMAL. Thanks for reminding me, Gus.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 15, 2008 at 8:58 am
Jeff Moden (2/15/2008)
Caught me? Heh... I jumped right out in front of ya 😀What you say is similar to what I used to say and think.
I know...It's just that this evokes that same involuntary reaction I had when I heard Roy got bit by a tiger (as in "well you DID have your head in its mouth at the time...."). It's hard to resent something for its defined behavior. Meaning - there really isn't anything past what you can "see" in a decimal, so - stop pretending that there is and a lot of the dissatisfaction goes away... Similar issue to the misconceptions re: floats, just reversing the expectations...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 15, 2008 at 9:05 am
Mr. Moden,
Thank you for expanding on the inaccuracies of the float type. Posting an actual result set with the decimals was a very good idea. I got me doing more tests.
The following is not to be taken as a criticism of your previous post. It is just that more questions now appear. And I really look forward to the feedback. This thread is certainly generating a lot of interest...
By the way, in my initial post I was issuing 100 shares to 3 shareholders, hence the 33.3333 number. Then I decided to make it 99 so it that the shareholders might receive equal amounts of share, but I forgot to remove the .3333 ...
OK Now, this is what I tried:
[font="Courier New"]SET NOCOUNT ON
DECLARE @lf_N1 FLOAT, @lf_N2 FLOAT, @lf_N3 FLOAT, @lf_N4 FLOAT, @lf_N_Total FLOAT
SET @lf_N_Total = 99.9999
SET @lf_N1 = 33.3333
SET @lf_N2 = 33.3333
SET @lf_N3 = 22.2222
SET @lf_N4 = 11.1111
SELECT @lf_N_Total AS Total
SELECT @lf_N1 AS N1
SELECT @lf_N2 AS N2
SELECT @lf_N3 AS N3
SELECT @lf_N4 AS N4
SELECT @lf_N1 + @lf_N2 + @lf_N3 + @lf_N4 AS Somme
IF @lf_N_Total - (@lf_N1 + @lf_N2 + @lf_N3 + @lf_N4) = 0.0
SELECT 'Bang On'
ELSE IF @lf_N_Total - (@lf_N1 + @lf_N2 + @lf_N3 + @lf_N4) < 0.0
SELECT 'Over Limit'
ELSE
SELECT 'Still got a few more'
SELECT 'Using the Float Values'
SELECT 99.999899999999997 , (33.333300000000001 + 33.333300000000001 + 22.222200000000001 + 11.1111) AS Somme
SELECT 99.999899999999997 - (33.333300000000001 + 33.333300000000001 + 22.222200000000001 + 11.1111) AS Diff
IF 99.999899999999997 - (33.333300000000001 + 33.333300000000001 + 22.222200000000001 + 11.1111) = 0.0
SELECT 'Bang On'
ELSE IF 99.999899999999997 - (33.333300000000001 + 33.333300000000001 + 22.222200000000001 + 11.1111) < 0.0
SELECT 'Over Limit'
ELSE
SELECT 'Still got a few more'
And the results are --- inconsistent
Total
-----------------------------------------------------
99.999899999999997
N1
-----------------------------------------------------
33.333300000000001
N2
-----------------------------------------------------
33.333300000000001
N3
-----------------------------------------------------
22.222200000000001
N4
-----------------------------------------------------
11.1111
Somme
-----------------------------------------------------
99.999899999999997
Bang On
Using the Float Values
------------------- Somme
------------------- ----------------------
99.999899999999997 99.999900000000003
Diff
-----------------------
-.000000000000006
Over Limit[/font]
Wut?
It also seems that when adding the local variables the same -0.000000000000003 occurs for the Somme just as for the original Total (99.9999 short by the same ...3
So Floats appear to give exact results but only because the binary representation of the Somme Float and the addition of the 4 local N variables ends up being the same.
Not very reassuring.
And on top of all this, the issue of splitting 100 shares equally among 3 shareholders
still fails.
It does not seem that Floats are reliable. In fact, I never compared Floats subtraction with 0.0 because it generally fails.
February 15, 2008 at 9:17 am
Jeff Moden (2/15/2008)Heh... it's deja-vue all over again... and a bit ironic... that's exactly what I used to recommend... (Matt, PLEASE take no offense... I'm NOT picking on you... it's just what I used to say almost word for word 😉 )
Ha, no offense taken, if I didn't want to expand my knowledge I wouldn't bother to read or post, and I can always learn something new.
However, your example is contrived to a degree. Because you are defining @d as decimal(4, 2), you naturally introduce rounding errors. Change it to decimal(6, 4) and the values in column 2 and 3 are identical. I know you are trying to prove the point, but if someone chooses the wrong datatype to do the job then they can expect an incorrect result.
I don't disagree per se with what you stated, and I certainly haven't done any performance analysis of decimal vs float, but I don't think you can apply the same logic to every situation. If a column is expected to hold exact amounts regardless of how it is displayed, then it should be stored in whatever precision is necessary. A transaction amount column holding amounts reported by the bank to 2 decimals needs to store just that. On the other hand, a variable or temp table column used for calculations needs to be defined based on the business requirement for the calculation, whether that be money, decimal or float.
As an example, consider an interest rate of 0.03172648 annually, and you are planning to determine expected interest earned over x days on amounts greater than 100 million - that rate needs to be exact or the rounding errors will add up quickly (even if the final amount might be displayed to 2 decimals). When dealing with money, users don't like that for some reason.
February 15, 2008 at 10:08 am
I think the issue of how to store money is different than the issue of how to cast it properly for calculations.
If you are doing financial calculations that use built-in SQL Server math functions you have to start by casting the data to float. The problem with conversions from decimal to float and back again is that float stores data as base 2 fractions. There is not always an exact way to convert from base 2 fractions to base 10 fractions without introducing a repeating decimal. This is really the source of the common myth that float is not exact or accurate. Usually you can resolve this with proper rounding of the data.
Financial people, on the other hand, want to work with exact dollars and cents, so I think in most cases you will need to store it in decimal or money. They don’t want to hear that 2.99999999999… is really 3.00. After all, you can’t write a check for $2.99999999999 or have that amount in your pocket.
As for the limitations of Crystal Reports, that is not something that I would let drive a database design. If a reporting tool doesn’t do a good job with the data, then you need a better reporting tool.
February 15, 2008 at 10:50 am
Matt S... yeaup... a bit "contrived" as you say... the example is meant to mimic the real world where people forget what datatype they're using to do calculations and the scale is insufficient for whatever they want to do.
J. -- Yes, you are correct... your "simple" additions do show an error... but that's because you've not treated the comparison as if it where a final answer... the final answer on both sides of the equation must be at the same scale. In your examples, you would need to do a Cast on both sides of the equation. Yeah, I know... seems overly complicated and, for many simple calculations, that would seem to be true.
Michael... I believe you've nailed the crux of what I'm so poorly trying to get across... I just need to come up with a better demonstrable example...
So, let me compromise with Matt Miller and a few other folks just to keep the innocent out of trouble... and a couple of good folks on this thread have already identified this... make sure you calculate your data with enough scale to produce the correct answers after rounding so that some of the implicit conversions don't cause unexpected inaccuracies.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 15, 2008 at 12:20 pm
About Crystal Reports.
I do not mind using CR's File -> Options ONCE formatting money-type fields to NOT display the currency symbol and specify that the negative values are display format as -123 instead of (123) because then they don't line up.
This works with both versions 8.5 and 9.
When I saw the changes made regarding the SQL statement between the two versions (v. 9 introduced the concept of "command" and the only parameters that would "stick" were those explicitely entered in the parameters editors, even if I did not use ALL the parameters required by the stored procedure. So by COMMENTING OUT the unneeded parameters in the WHERE clause, believe it or not, this works.
Afraid of further version quirks requiring unnatural workarounds, I stopped upgrading.
What I have works and there is no need for further change.
February 28, 2008 at 2:36 am
sir,i want to store currency symbols in database sql server but the symbols are stored like we store ??? for rubles but it stores it as ?????±then what is the way to store it correctly
February 28, 2008 at 9:12 pm
Currency symbols are a form of formatting... and that's a form of "Death by SQL". You shouldn't ever store formatted data in a database. Formatting should either be done by the GUI or when you display for reporting purposes.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 28, 2008 at 10:06 pm
Hmm - I missed this thread a couple of weeks ago 🙂 I have to say Jeff that your example is a bit contrived as decimal(4,2) is not the same type as decimal(6,4) for example. The wrong type was used for the job thus you got an incorrect answer. I can the point that some of the MS SQL functions probably use floating point internally anyway.
Good reading for all involved is at http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems and then the following section on minimising such issues. 🙂
February 29, 2008 at 3:46 am
Ian Yates (2/28/2008)
The wrong type was used for the job thus you got an incorrect answer
THAT, Sir, was the whole point! Glad someone finally got that 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 29, 2008 at 6:27 am
In Crystal, you have control of the display field format and can set the $ not to display. I personally don't like a column of 50 numbers each with a '$' in front of it...very repetitive, adds little extra value, and clutters up the data. I usually note that it is currency in the column header similar to how you might denote other units.
Salary($), Weight (lbs), Length(mm), etc. Makes the data easier to read. If it is a single field (not a column of values), you can also just put a label next to it with a '$'.
I don't use the 'money' or 'smallmoney' datatypes in the database either, and in the past have used 'decimal' for money amounts, but after reading the posts here, I may have to change my evil ways.
I have also had to work behind someone who stored formatting in the database, and wouldn't you know, I had to use the same data, but it needed to be formatted differently...what a PITA stripping the formatting on the fly not to mention the overhead that adds. There are plenty of ways to format data outside of the database...please don't do it inside.
Thanks for the good discussion on this...it is a very real and important issue.
If it was easy, everybody would be doing it!;)
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply