About Floating Point

Most of us are familiar with integers, which map directly onto words in computer memory. Floating-point format is a way to use those words to represent non-integer numbers with fractional parts. For example, 3.142 is stored in two pieces, the raw number "3142" and "1 place". The result "3.142" is called "floating point" because the decimal point floats or moves to accommodate value changes during expression evaluations. By contrast, in fixed-point arithmetic, the number of decimal places remains constant.

You can use the float datatype to assign a floating-point format to the data in a table column. Keep in mind, however, that float is an approximate numeric datatype, as defined by ANSI standards. It stores slightly imprecise representations of real numbers as binary fractions at the hardware level. The accuracy of float datatypes is limited by the number of bits used to represent the abscissa, the part raised to the power represented by the mantissa. When the representations are displayed, printed, transferred between hosts, or used in calculations, they lose precision.

When to Avoid Using float

Do not use the float datatype where absolute precision is required. This section gives examples of types of situations to avoid.

Financial Applications

Programmers writing financial applications often mistakenly use floating-point datatypes to represent monetary values, such as dollars and cents. Doing so results in mysterious rounding errors where the total of a column is slightly different from the exact arithmetic sum of all integer values that represent the currency amounts.

For exact representation of decimal fractions, use one of the following datatypes, as appropriate to your needs:

• numeric and decimal datatypes. These two datatypes are identical, except that you can use numeric types with a scale of 0 for the IDENTITY column. For details on these exact numeric datatypes and their optional parameters, see the Transact-SQL User's Guide.

• money and smallmoney datatypes. These two datatypes are accurate to one ten-thousandth of a monetary unit. For display purposes only, they round up to two decimal places.

  Remember, monetary values entered with E notation are interpreted as float, which can cause the entry to be rejected or to lose precision when stored as money or smallmoney.

Loop Indexes

Do not use floating-point variables as loop indexes. For example, you would not use:

DECLARE counter float;
counter = 0;
while (counter <1) loop
<do something>
counter = counter + 0.1;
end loop

Adding 0.1 ten times to a floating-point variable initialized to zero would not give a final value of 1.0. The result is very close to 1.0, but not exactly equal. Instead you would use:

DECLARE counter integer;
counter = 0;
while (counter <10) loop
<do something>
counter = counter + 1;
end loop

IEEE Format

The double precision datatype, one of the approximate numeric datatypes for storing floating-point numbers, is the most accurate server floating-point representation. A double precision floating-point number is stored in 8 bytes.

In UNIX and Alpha environments, SQL Server and Adaptive Server use the IEEE format for floating-point numbers, a total of 64 bits for any value: 1 bit for the sign, 11 bits for the exponent, and the other 52 bits for precision. Two floats must differ by one (2 to the 52nd power), which is about 17 decimal digits, so that their representation differs.

Some decimal numbers, however, cannot be represented exactly by binary numbers. For example, the floating-point number 1.20003 may be interpreted as 1.20029999999999930082594801206. Notice that the sixth decimal digit differs, although the stated accuracy of 17 decimals is true.

IEEE is fairly accurate to within 16 decimal places. A floating-point value's mantissa is a fractional value, impossible to represent adequately with a binary number. For values that are not exact multiples of 2^-n power, precision can be lost. Also, conversion of ASCII representations of float into IEEE format is inaccurate in the last part of the number. For example, 0.000000000000987654320999999935 becomes 0.000000000000987654321000000137.

Alpha Platforms

SQL Server and Adaptive Server on Alpha platforms use IEEE single precision (S-floating) and IEEE double precision (T_floating) for the 4-byte and 8-byte floating datatypes, respectively.

For single precision (S-floating), the Alpha approximation of 0.1 is 1.60000002384185791015625 * 2^-4 = 1.60000002384185791015625 / 16 = 0.100000001490116119384765625.