Let me first say that I have never heard of Stimulsoft before, so I can't speak to that specifically. However, the problem is generic to the field of computing.
Numbers can be represented in a computer in different ways. Exact integer numbers are stored as such, except that it is in the base of 2 and not 10, that is binary format - everything in computers is stored in binary.
However, since these numbers are typically fixed length, there is only a certain range that can be stored. Exactly how long that range is depends on the number of bits being used. For 32 bits, the upper range is 2147483647.
This means that for larger numbers we need a different type, and the same is true if we also want to have decimal values. Computers typically offers two solutions here: exact decimal values and approximate floating point values. The latter has the advantage that they can cover really wide range. A 64-bit floating-point number can range from 1E-308 to 1E308. The drawback is that they are not exact. Which is not much of an issue if you work with scientific data. There are few scientific calculations that require as much as 15 digits of precision, which is what you get with a 64-bit float. Even the precision with a 32-bit floating point number of 7-8 digits is perfectly adequate for the very most applications for nuclear physics, engineering etc. The maximum range of 1E24 can be a limitation, though.
If you are working with financial data, it's a completely different matter. You certainly do not need that wide range of 64-bit floats, and you would have fairly extreme situations if you hit the ceiling with 32-bit floating point values. On the other hand, precision is very important. Just because the amount is over 100 million does not mean that you can accept an error in the last few digits, but the values need to be exact to the last digit.
For this reason, 32-bit floating point values (the data type real
in SQL Server) are entirely inappropriate for storing amounts. 64-bit values (float
in SQL Server) usually works, because 14-15 is usually enough. But you have to be very careful and know what you are doing. (I've worked with a financial system for many years where we use float
for amounts.)
As Sree points out, your column is float
, and the value 123456789 should certainly appear as such with float,
and it is not surprising that it works in other environments. As I said, I have never heard of Stimulsoft before, but it appears that it uses 32-bit floating point values, and a nine-digit value cannot be represented exactly in a 32-bit floating point value.
I don't know why you picked float
for your Price column, but it was probably an incorrect choice. You should probably have picked the decimal
data type instead. Not that this type is without its own problem. In SQL Server, you will need to settle for a maximum number of digits you support and now many decimals. But at least you will not get rounding problems of this kind.