0

I am using sql server 2005 and .NET. I have a table with float column named "price". When I stores 1890 it store 1889.99966796875.

What is the reason?

Naor
  • 23,465
  • 48
  • 152
  • 268
  • 2
    `Money` for column type in SQL Server and `System.Decimal` as type in .NET, when working with money/currency as I believe your value is. – Johan Apr 12 '11 at 14:14

3 Answers3

5

A floating point number is a special data type used for storing numbers of widely varying ranges. The trade off is in precision - which is what you are seeing as a problem. Floating point numbers

You may want to store your values with the decimal data type, or money instead.

IKEA Riot
  • 111
  • 2
  • how does money and decimal are treated in .net? can I load them into double variable? – Naor Apr 12 '11 at 14:10
  • Best not to treat them as doubles in .Net or you'll experience the same problems. Money and Decimal SQL types are treated as Decimal in .Net. – IKEA Riot Apr 12 '11 at 14:32
3

The reason that floating numbers cannot be mapped to all numbers is because of the mismatch between the decimal and the binary system for fractions.

1889.99966796875 is the number that is the closest to 1890 in this floating point domain, so the closest this number can be reached using binary representation.

Other types, such as decimal and money, use other, much more memory consuming, techniques to store the number (for example , in a string you can store any number, but of course this is not the most performant way to do math)

a simple example : 0.3 in my own binary system : 

0.1b (inary) would be 0.5 d (ecimal) so too much...
0.01b -->  0.25d (1/4 too little)
0.011 -->  0.375 (1/4 + 1/8 too much)
0.0101 --> 0.3125 (1/4 + 1/16 still too much)
...
0.010011 --> 1/4 +1/32 + 1/64 = 0.296875

Suppose my system has 6 bits to represent the fraction, 0.296875 would be the closest for this domain. The right number cannot be reached due to the decimal/binary system.

For examples see also : Floating point inaccuracy examples

And an excellent elaborate explenation of your problems is to be found here: http://download.oracle.com/docs/cd/E19957-01/806-3568/ncg_goldberg.html

Another note : it is really about mismatch, not about 'quality' of systems : in decimal notation for example, you cannot represent 1/3 100% accurate, while this would be perfectly possible in other systems.

Community
  • 1
  • 1
Peter
  • 47,963
  • 46
  • 132
  • 181
2

Any number that you want to store an exact number for or do math calculations on should be stored as decimal or number not float. This is an inexact data type and it will cause rounding errors and incorrect calculations. In general, float should be avoided.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • That's not true, fp is in the first place for math. Eg. it is often used in statistics due to the performance penalty of the other types, you just have to know if the accuracy throughout calculations stay within desired limits. Fp is not just out there to not use it. – Peter Apr 12 '11 at 17:03
  • Sorry but unless you have data that is too large (or too samll ) for decimal, the rounding problems with float make it unsuitable for math in a database. – HLGEM Apr 12 '11 at 17:48
  • Sorry, fp *is* correctly used in a lot of Math It has nothing to do with larger or small, but with significant figures. In short : If you need 5 of them and the fp. is supplying 10, (applied) correctly used operations have no problem at all :the rounding problems occur after the significant figures then, be it before or after the decimal point. But for discrete math they're indeed not suitable. – Peter Apr 12 '11 at 19:24