Hugo Kornelis (11/19/2012)
But I "like" this because it shows up a terrible disdain in SQL Server for handling basic mathematics under certain circumstances. I don't like the "it's a floating data type which is always an approximate value" explanation that is routinely given as the reason for this sloppiness. Millions of hand calculators have been storing numbers as floats for decades and don't do things like this.
Do you have a source to confirm that hand calculators store numbers as floats rather than fixed point numbers? (I have never seen any documentation on internals of hand calculators, but I think they use a representation that is neither of the above, but a representation that avoids binary representation while still having a floating point to enable numbers with high mantissa to be stored).
Thank you Hugo for that post back. Seldom is the occasion when I fail to learn something from your comments.
You know, in fact I don't have any such source, nor can I locate one with Google. My memory is quite clear that the manuals that came with my calculators referred to floating point arithmetic, but that could be (a) an artifact of my faulty memory (my own, physiologically internal, memory storage problem!) or (b) inexact language in the manual, referring to its internal storage as "floating point" when it really isn't. Either way, you may well be right that the internal storage of numbers in calculators is different from what SQL Server uses.
Edited Addition: Bingo, Hugo: Just found this link that seems to support your point that calculators use a different memory storage.
If so, it would be handy to enable "calculator-mode" for a variety of simple math problems in SQL Server. Take that code you posted, for example. I certainly understand that trying to represent 1/3 in binary storage is an impossibility, and it makes sense that the first batch using NUMERIC(12,10) doesn't yield a correct answer. What I'm unclear about is whether one could have predicted -- ahead of time, with certainty -- that the second batch using FLOAT would correctly return 10?
And Tom, thanks for this bit at the end of your post, which I think is a clearer explanation:
What we have here is decimal rounding done by STR - which is to the closest value, while SQL Servers implementation of the binary rounding in floating point is always up, as stated on that BoL page. Of course the value handed to STR isn't 3.65, it's 3.64999999999999 accurate to 15 decimal digits (maybe even 3.6499999999999 accurate to 16) so it is actually less than 3.65, which is why STR delivers 3.6 instead of 3.7.