Cliff Jones (8/6/2011)
SQLkiwi (8/5/2011)
Tom.Thomson (8/5/2011)
I would hope that most people realise that in applications where monetary values range from 0.01 units to 90071992547409.92 units (something over nine hundred million million units), and no greater accuracy than two places after the point is needed, float (which is a synonym for float(53)) is usually far more storage efficient and usually far mor eperformance efficient than any decimal or money type, and no less accurate. Let's hope people also realise that that covers the vast majority of applications involving monetary values.Excellent point! FLOAT is used extensively at the financial institution I am currently engaged at for precisely (ha!) these reasons, especially performance. For anyone wondering, 90071992547409.92 is 253 / 100.
I agree, I learned something. I don't deal much with financial data but thought that it was a mistake to use approximate data types for money. Don't you have difficulty with being slightly off when performing calculations?
If you count cents (so that 1 dollar is represented by the number 100, not by 1) float gives exact representation for all multiples of 1 cent between $-90071992547409.92 and $90071992547409.92 inclusive, so you won't be any more off when performing calculations than you would have been with numeric(16,2). Your storage is 8 bytes per number instead of 9. If you represent a dollar by the number 1 you may have some problems. I know that some financial institutions are perfectly happy to live with those problems - maybe others are not.
Of course you may have difficulties in some areas because the float calculations don't produce fixed rounding errors as rapidly as the numeric(16,2) ones - maybe you want (sum (x/3)) to be different from sum(x)/3 because the rules for the caculation require committing the rounding error caused by sticking to the fixed accuracy at each individual division or multiplication. So maybe bigint would be a better representation than float if most of your calculations are like that. But of course you can always commit the rounding errors by casting out and back when you want to fix them, and maybe only some of your calculations require it.
Tom