Be aware of these points when using numeric data types.
The INTEGER, NUMERIC, and DECIMAL data types are sometimes called exact numeric data types, in contrast to the approximate numeric data types FLOAT, DOUBLE, and REAL. Only exact numeric data is guaranteed to be accurate to the least significant digit specified after arithmetic operations.
Do not fetch TINYINT columns into Embedded SQL variables defined as CHAR or UNSIGNED CHAR, since the result is an attempt to convert the value of the column to a string and then assign the first byte to the variable in the program.
Numeric Data Type |
Description |
|---|---|
BIGINT |
A signed 64-bit integer, requiring 8 bytes of storage. You can specify integers as UNSIGNED. By default the data type is signed. Its range is between -9223372036854775808 and 9223372036854775807 (signed) or from 0 to 18446744073709551615 (unsigned). |
INT or INTEGER |
A signed 32-bit integer with a range of values between -2147483648 and 2147483647 requiring 4 bytes of storage. The INTEGER data type is an exact numeric data type; its accuracy is preserved after arithmetic operations. You can specify integers as UNSIGNED; by default the data type is signed. The range of values for an unsigned integer is between 0 and 4294967295. |
SMALLINT |
A signed 16-bit integer with a range between -32768 and 32767, requiring 2 bytes of storage. The SMALLINT data type is an exact numeric data type; its accuracy is preserved after arithmetic operations. |
TINYINT |
An unsigned 8-bit integer with a range between 0 and 255, requiring 1 byte of storage. The TINYINT data type is an exact numeric data type; its accuracy is preserved after arithmetic operations. |
DECIMAL |
A signed decimal number with precision total digits and with scale of the digits after the decimal point. The precision can equal 1 to 126, and the scale can equal 0 up to precision value. The defaults are scale = 38 and precision = 126. Results are calculated based on the actual data type of the column to ensure accuracy, but you can set the maximum scale of the result returned to the application using the MAX_CLIENT_NUMERIC_SCALE option. |
NUMERIC |
Same as DECIMAL. |
DOUBLE |
A signed double-precision floating-point number stored in 8 bytes. The range of absolute, nonzero values is between 2.2250738585072014e-308 and 1.797693134862315708e+308. Values held as DOUBLE are accurate to 15 significant digits, but might be subject to rounding errors beyond the fifteenth digit. The DOUBLE data type is an approximate numeric data type; it is subject to rounding errors after arithmetic operations. |
FLOAT |
If precision is not supplied, the FLOAT data type is the same as the REAL data type. If precision supplied, then the FLOAT data type is the same as the REAL or DOUBLE data type, depending on the value of the precision. The cutoff between REAL and DOUBLE is platform-dependent, and it is the number of bits used in the mantissa of single-precision floating point number on the platform. When a column is created using the FLOAT data type, columns on all platforms are guaranteed to hold the values to at least the specified minimum precision. In contrast, REAL and DOUBLE do not guarantee a platform-independent minimum precision. The FLOAT data type is an approximate numeric data type; it is subject to rounding errors after arithmetic operations. |
REAL |
A signed single-precision floating-point number stored in 4 bytes. The range of absolute, nonzero values is 1.175494351e-38 to 3.402823466e+38. Values held as REAL are accurate to 6 significant digits, but might be subject to rounding errors beyond the sixth digit. The REAL data type is an approximate numeric data type; it is subject to rounding errors after arithmetic operations. |
This table lists the storage required for a decimal number.
Precision |
Storage |
|---|---|
1 to 4 |
2 bytes |
5 to 9 |
4 bytes |
10 to 18 |
8 bytes |
19 to 126 |
See below |
The storage requirement in bytes for a decimal value with a precision greater than 18 can be calculated using the following formula:
4 + 2 * (int(((prec - scale) + 3) / 4) + int((scale + 3) / 4) + 1)
where int takes the integer portion of its argument. The storage used by a column is based upon the precision and scale of the column. Each cell in the column has enough space to hold the largest value of that precision and scale. For example:
NUMERIC(18,4) takes 8 bytes per cell NUMERIC(19,4) takes 16 bytes per cell
The DECIMAL data type is an exact numeric data type; its accuracy is preserved to the least significant digit after arithmetic operations. Its maximum absolute value is the number of nines defined by [precision - scale], followed by the decimal point, and then followed by the number of nines defined by scale. The minimum absolute nonzero value is the decimal point, followed by the number of zeros defined by [scale - 1], then followed by a single one. For example:
NUMERIC (3,2) Max positive = 9.99 Min non-zero = 0.01 Max negative = -9.99
SELECT CAST( NULL AS NUMERIC ) A, CAST( NULL AS NUMERIC(15,2) ) Bis described as:
A NUMERIC(1,0) B NUMERIC(15,2)