Reference Manual
CHAPTER 7. SQL Data Types
For storing numerical data.
The NUMERIC and DECIMAL data types, and the various kinds of INTEGER data type, are sometimes called exact numeric data types, in contrast to the approximate numeric data types FLOAT, DOUBLE, and REAL.
The exact numeric data types are those for which precision and scale values can be specified, while approximate numeric data types are stored in a predefined manner. Only exact numeric data is accurate guaranteed accurate to the least significant digit specified after an arithmetic operation.
TINYINT columns should not be fetched 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.
Before release 5.5, hexadecimal constants longer than four bytes were treated as string constants, and others were treated as integers. The new default behavior is to treat them as binary type constants. To use the historical behavior, set the TSQL_HEX_CONSTANTS database option to OFF.
Data type lengths and precision of less than one are not allowed.
In Embedded SQL, TINYINT columns should be fetched into 2-byte or 4-byte integer columns. Also, to send a TINYINT value to a database, the C variable should be an integer.
Only the NUMERIC data type with scale = 0 can be used for the Transact-SQL identity column.
You should avoid default precision and scale settings for NUMERIC and DECIMAL data types, because these are different Adaptive Server Anywhere and Adaptive Server Enterprise. In Adaptive Server Anywhere, the default precision is 30 and the default scale is 6. In Adaptive Server Enterprise, the default precision is 18 and the default scale is 0.
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 Adaptive Server Anywhere, the cutoff is platform-dependent, but on all platforms the cutoff value is greater than 15.
BIGINT data type [Numeric]A signed 64-bit integer.
[ UNSIGNED ] BIGINT
The BIGINT data type is an exact numeric data type: its accuracy is preserved after arithmetic operations.
A BIGINT value requires 8 bytes of storage.
The range for signed BIGINT values is from ( -2e63 + 1 ) to ( +2e63 -1 ).
The range for unsigned BIGINT values is from 0 to ( 2e64 - 1 ).
By default, the data type is signed.
SQL/92 Vendor extension.
Sybase Not supported in Adaptive Server Enterprise.
DECIMAL data type [Numeric]A decimal number with precision total digits and with scale of the digits after the decimal point.
{ DECIMAL | DEC } [ ( precision [ , scale ] ) ]
The DECIMAL data type is an exact numeric data type; its accuracy is preserved to the least significant digit after arithmetic operations.
The storage required for a decimal number can be estimated as:
2 + int( ( before+1 ) / 2 ) + int( ( after + 1 )/2 )
where int takes the integer portion of its argument, and before and after are the number of significant digits before and after the decimal point. The storage is based on the value being stored, not on the maximum precision and scale allowed in the column.
precision An integer expression that specifies the number of digits in the expression. The default setting is 30.
scale An integer expression that specifies the number of digits after the decimal point. The default setting is 6.
The defaults can be changed by setting database options. For information, see PRECISION option and SCALE option.
SQL/92 Compatible with SQL/92.
Sybase Compatible with Adaptive Server Enterprise.
DOUBLE data type [Numeric]A double-precision floating-point number.
DOUBLE [ PRECISION ]
The DOUBLE data type holds a double-precision floating point number.
It is an approximate numeric data type; subject to roundoff errors after arithmetic operations. The approximate nature of DOUBLE values means that queries using equalities should generally be avoided when comparing DOUBLE values.
DOUBLE values require 8 bytes of storage.
The range of values is 2.22507385850721e-308 to 1.79769313486231e+308. Values held as DOUBLE are accurate to 15 significant digits, but may be subject to round-off error beyond the fifteenth digit.
SQL/92 Compatible with SQL/92.
Sybase Compatible with Adaptive Server Enterprise.
FLOAT data type [Numeric]A floating point number, which may be single or double precision.
FLOAT [ ( precision ) ]
When a column is created using the FLOAT ( precision ) 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.
If precision is not supplied, the FLOAT data type is a single precision floating point number, equivalent to the REAL data type, and requires 4 bytes of storage.
If precision is supplied, the FLOAT data type is either single or double precision, depending on the value of precision specified. The cutoff between REAL and DOUBLE is platform-dependent. Single precision FLOATs require 4 bytes of storage, and double precision FLOATs require 8 bytes.
The FLOAT data type is an approximate numeric data type. It is subject to roundoff errors after arithmetic operations. The approximate nature of FLOAT values means that queries using equalities should generally be avoided when comparing FLOAT values.
precision An integer expression that specifies the number of places after the decimal.
SQL/92 Compatible with SQL/92.
Sybase You can tune the behavior of the FLOAT data type for compatibility with Adaptive Server Enterprise, using the FLOAT_AS_DOUBLE option.
INT or INTEGER data type [Numeric]An integer value requiring 4 bytes of storage.
[ UNSIGNED ] { INT | INTEGER }
The INTEGER data type is an exact numeric data type; its accuracy is preserved after arithmetic operations.
If you specify UNSIGNED; the integer can never be assigned a negative number. By default, the data type is signed.
The range for signed integers is from ( -2e31 + 1 ) to ( +2e31 -1 ).
The range for unsigned integers is from 0 to ( 2e32 - 1 ).
SQL/92 Compatible with SQL/92. The UNSIGNED keyword is a vendor extension.
Sybase The signed data type is compatible with Adaptive Server Enterprise. Adaptive Server Enterprise does not support the UNSIGNED data type.
NUMERIC data type [Numeric]Same as DECIMAL.
NUMERIC [ ( precision [ , scale ] ) ]
The NUMERIC data type is an exact numeric data type; its accuracy is preserved to the least significant digit after arithmetic operations.
The storage required for a decimal number can be estimated as:
2 + int( (before+1) / 2 ) + int( (after+1)/2 )
where int takes the integer portion of its argument, and before and after are the number of significant digits before and after the decimal point. The storage is based on the value being stored, not on the maximum precision and scale allowed in the column.
precision An integer expression that specifies the number of digits in the expression. The default value is 30.
scale An integer expression that specifies the number of digits after the decimal point. The default value is 6.
The defaults can be changed by setting database options. For information, see PRECISION option and SCALE option.
SQL/92 Compatible with SQL/92, if the SCALE option is set to zero.
Sybase Compatible with Adaptive Server Enterprise.
REAL data type [Numeric]A single-precision floating-point number stored in 4 bytes.
REAL
The REAL data type is an approximate numeric data type; it is subject to roundoff errors after arithmetic operations.
The range of values is 1.175495e-38 to 3.402823e+38. Values held as REAL are accurate to 10 significant digits, but may be subject to round-off error beyond the sixth digit.
The approximate nature of REAL values means that queries using equalities should generally be avoided when comparing REAL values
SQL/92 Compatible with SQL/92..
Sybase Compatible with Adaptive Server Enterprise.
SMALLINT data type [Numeric]Integer value requiring 2 bytes of storage.
[ UNSIGNED ] SMALLINT
The SMALLINT data type is an exact numeric data type; its accuracy is preserved after arithmetic operations. It requires 2 bytes of storage.
The range for signed SMALLINT values is from -32768 to + 32767.
The range for unsigned SMALLINT values is from zero to 65535.
SQL/92 Compatible with SQL/92. The UNSIGNED keyword is a vendor extension.
Sybase The signed data type is compatible with Adaptive Server Enterprise. Adaptive Server Enterprise does not support the UNSIGNED data type.
TINYINT data type [Numeric]Unsigned integer, requiring 1 byte of storage.
[ UNSIGNED ] TINYINT
The TINYINT data type is an exact numeric data type; its accuracy is preserved after arithmetic operations.
You can explicitly specify TINYINT as UNSIGNED, but the UNSIGNED modifier has no effect as the type is always unsigned.
The range for TINYINT values is from zero to 255.
SQL/92 Vendor extension.
Sybase Compatible with Adaptive Server Enterprise.