SQL anti-Pattern Learning Note 10 rounding error

2014-10-13 16:44:28

goal : replace integers with decimals

anti-pattern : Using float type

According to the IEEE754 identity, the float type encodes real data in binary format.

Cons: (1) The necessity of rounding: not all of the information described in the decimal can be stored using binary, in some necessary factors,

A floating-point number is usually rounded to a very close value.

Example: Select rate from A where id=123--result:59.95

SELECT * from A where rate=59.95--result:empty set;no rows match.

SELECT * from A where ABS (rate-59.95) <0.000001-this ability to correctly detect data!

(2) Treat floating-point numbers as "approximate equals"

how to recognize anti-patterns : Any design that uses a float, real, or double percent type can be an anti-pattern

Rational use of anti-pattern : If the value to be stored is a large range, greater than the range of integer, numeric, it can only be used float. Programs for scientific computing classes typically use float.

solution : Use numeric, decimal type

using numeric, decimal instead of float, they do not round the stored rational number, so select * from A where rate=59.95 returns records.

Note: Numeric, decimal is exactly the same in SQL Server and even Sybase.

conclusion : Do not use floating-point numbers whenever possible

SQL anti-Pattern Learning Note 10 rounding error