Decimal datatypes

Adaptive Server provides two other exact numeric datatypes, numeric and dec[imal], for numbers that include decimal points. Data stored in numeric and decimal columns is packed to conserve disk space, and preserves its accuracy to the least significant digit after arithmetic operations. The numeric and decimal datatypes are identical in all respects but one: only numeric datatypes with a scale of 0 can be used for the IDENTITY column.

Specifying precision and scale

The numeric and decimal datatypes accept two optional parameters, precision and scale, enclosed in parentheses and separated by a comma:

datatype [(precision [, scale])]

Adaptive Server treats each combination of precision and scale as a distinct datatype. For example, numeric(10,0) and numeric(5,0) are two separate datatypes. The precision and scale determine the range of values that can be stored in a decimal or numeric column:

Storage size

The storage size for a numeric or decimal column depends on its precision. The minimum storage requirement is 2 bytes for a 1- or 2-digit column. Storage size increases by approximately 1 byte for each additional 2 digits of precision, up to a maximum of 17 bytes.

Use the following formula to calculate the exact storage size for a numeric or decimal column:

ceiling (precision / log 256 ) + 1

For example, the storage size for a numeric(18,4) column is 9 bytes.

Entering decimal data

Enter decimal and numeric data as a string of digits preceded by an optional plus or minus sign and including an optional decimal point. If the value exceeds either the precision or scale specified for the column, Adaptive Server returns an error message. Exact numeric types with a scale of 0 are displayed without a decimal point.

Table 1-8 shows some valid entries for a column with a datatype of numeric(5,3) and indicates how these values are displayed by isql:

Table 1-8: Valid decimal values

Value entered

Value displayed

12.345

12.345

+12.345

12.345

-12.345

-12.345

12.345000

12.345

12.1

12.100

12

12.000

Table 1-9 shows some invalid entries for a column with a datatype of numeric(5,3):

Table 1-9: Invalid decimal values

Value entered

Type of error

1,200

Commas not allowed.

12-

Minus sign should precede digits.

12.345678

Too many nonzero digits to the right of the decimal point.