For storing numerical data.
[ UNSIGNED ] BIGINT
[ UNSIGNED ] { INT | INTEGER }
SMALLINT
TINYINT
DECIMAL [ ( precision [ , scale ] ) ]
NUMERIC [ ( precision [ , scale ] ) ]
DOUBLE
FLOAT [ ( precision ) ]
REAL
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. For more information, see the “MAX_CLIENT_NUMERIC_SCALE option” and the SET OPTION statement.
Table 4-2 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
If neither precision nor scale is specified for the explicit conversion of NULL to NUMERIC, the default is NUMERIC(1,0). For example,
SELECT CAST( NULL AS NUMERIC ) A, CAST( NULL AS NUMERIC(15,2) ) B
is described as:
A NUMERIC(1,0) B NUMERIC(15,2)
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.
You can tune the behavior of the FLOAT data type for compatibility with Adaptive Server Enterprise using the “FLOAT_AS_DOUBLE option [TSQL]”.
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.
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.
If a join column is a REAL data type, you must set FLOAT_AS_DOUBLE to OFF when creating join indexes, or an error occurs. Issues may also result when using inexact numerics for join columns.
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.
The CMP and HNG index types do not support the FLOAT, DOUBLE, and REAL data types, and the HG index type is not recommended.
The WD, DATE, TIME, and DTTM index types do not support the numeric data types.
In embedded SQL, fetch TINYINT columns into 2-byte or 4-byte integer columns. Also, to send a TINYINT value to a database, the C variable should be an integer.
Adaptive Server Enterprise 12.5.x versions do not support unsigned integers. You can map Sybase IQ unsigned integers to Adaptive Server Enterprise signed integers or numeric data, and the data are converted implicitly.
Map IQ UNSIGNED SMALLINT data to ASE INT
If you have negative values, map IQ UNSIGNED BIGINT to ASE NUMERIC (precision, scale)
To avoid performance issues for cross-database joins on UNSIGNED BIGINT columns, the best approach is to cast to a (signed) BIGINT on the Sybase IQ side.
You should avoid default precision and scale settings for NUMERIC and DECIMAL data types, as these differ by product:
Database |
Default precision |
Default scale |
|---|---|---|
Sybase IQ |
126 |
38 |
Adaptive Server Enterprise |
18 |
0 |
Adaptive Server Anywhere |
3 |
6 |
The FLOAT ( p ) data type is a synonym for REAL or DOUBLE, depending on the value of p. For Adaptive Server Enterprise, REAL is used for p less than or equal to 15, and DOUBLE for p greater than 15. For Sybase IQ, the cutoff is platform-dependent, but on all platforms, the cutoff value is greater than 22.
Sybase IQ includes two user-defined data types, MONEY and SMALLMONEY, which are implemented as NUMERIC(19,4) and NUMERIC(10,4) respectively. They are provided primarily for compatibility with Adaptive Server Enterprise.
