Fixed-precision numbers: DECIMAL and MONEY

Most commercial applications store numbers that have fixed numbers of digits on the right and left of the decimal point. For example, amounts in U.S. currencies are written with two digits to the right of the decimal point. Normally, you also know the number of digits required on the left, depending on the kinds of transactions that are recorded: perhaps 5 digits for a personal budget, 7 digits for a small business, and 12 or 13 digits for a national budget.

These numbers are fixed-point numbers because the decimal point is fixed at a specific place, regardless of the value of the number. The DECIMAL(p,s) data type is designed to hold decimal numbers. When you specify a column of this type, you write its precision (p) as the total number of digits that it can store, from 1 to 32. You write its scale (s) as the number of those digits that fall to the right of the decimal point. (The following figure shows the relation between precision and scale.) Scale can be zero, meaning it stores only whole numbers. When only whole numbers are stored, DECIMAL(p,s) provides a way of storing integers of up to 32 digits.

Like the DECIMAL(p) data type, DECIMAL(p,s) takes up space in proportion to its precision. One value occupies (p +3)/2 bytes (if scale is even) or (p + 4)/2 bytes (if scale is odd), rounded up to a whole number of bytes.

The MONEY type is identical to DECIMAL(p,s) but with one extra feature. Whenever the database server converts a MONEY value to characters for display, it automatically includes a currency symbol.

The advantages of DECIMAL(p,s) over INTEGER and FLOAT are that much greater precision is available (up to 32 digits as compared to 10 digits for INTEGER and 16 digits for FLOAT), and both the precision and the amount of storage required can be adjusted to suit the application.

The disadvantages of DECIMAL(p,s) are that arithmetic operations are less efficient and that many programming languages do not support numbers in this form. Therefore, when a program extracts a number, it usually must convert the number to another numeric form for processing.