Adjustable-precision floating point: DECIMAL(p)

In a database that is not ANSI-compliant, the DECIMAL(p) data type is a floating-point data type similar to FLOAT and SMALLFLOAT. The important difference is that you specify how many significant digits it retains. The precision you write as p can range from 1 to 32, from fewer than SMALLFLOAT up to twice the precision of FLOAT. The magnitude of a DECIMAL(p) number can range from 10-130 to 10124. The storage space that DECIMAL(p) numbers use depends on their precision; they occupy 1 + p/2 bytes (rounded up to a whole number, if necessary).

In an ANSI-compliant database, however, DECIMAL(p) is a fixed-point data type with a scale of zero, so DECIMAL(p) always stores integer values of precision p, if the data value has p or more significant digits. Any fractional part is truncated.

Do not confuse the DECIMAL(p) data type with the DECIMAL(p,s) data type, which is explained in the next section. The DECIMAL(p) data type has only the precision specified.

The DECIMAL(p) data type has the following advantages over FLOAT:
  • Precision can be set to suit the application, from approximate to precise.
  • Numbers with as many as 32 digits can be represented exactly.
  • Storage is used in proportion to the precision of the number.
  • Every Informix® database server supports the same precision and range of magnitudes, regardless of the host operating system.
The DECIMAL(p) data type has the following disadvantages:
  • Performance of arithmetic operations and sorts on DECIMAL(p) values is somewhat slower than on FLOAT values.
  • Many programming languages do not support the DECIMAL(p) data format in the same way that they support FLOAT and INTEGER. When a program extracts a DECIMAL(p) value from the database, it might have to convert the value to another format for processing.
  • The format and value of a DECIMAL(p) data type depends on whether the database is ANSI-compliant.