SQL Data Types

Data type specifies the set of possible values for a table column, variable, routine parameter or a function value. These types are available in 602SQL:

type ::= character_string [language_and_charset] | binary_object | bit_string | accurate_number_type 
    | approximate_number_type | BOOLEAN | DATE | TIME [WITH TIME ZONE] | TIMESTAMP [WITH TIME ZONE] 
    | domain_identifier
character_string ::= CHAR [( length )] | CHAR VARYING ( length ) | VARCHAR ( length ) | NCHAR [( length )] 
    | NCHAR VARYING ( length ) | CLOB [( length )] | NCLOB [( length )]
binary_object ::= BLOB [( length )]
bit_string ::= BINARY ( length ) | VARBINARY ( length ) | BIT [( length )] | BIT VARYING ( length )
accurate_number_type ::= INTEGER | SMALLINT | TINYINT | BIGINT | DECIMAL [( precision [, decim ] )] 
    | NUMERIC [( precision [, decim ] )]
approximate_number_type ::= REAL | DOUBLE PRECISION | FLOAT [( precision )]

Without changing a meaning you can use INT instead of INTEGER, DEC instead of DECIMAL, CHARACTER instead of CHAR, NATIONAL CHAR or NATIONAL CHARACTER instead of NCHAR, CHAR LARGE OBJECT or CHARACTER LARGE OBJECT instead of CLOB and BINARY LARGE OBJECT instead of BLOB.

You can also use LONG VARCHAR instead of CLOB, LONG VARBINARY instead of BLOB and VARBINARY instead of BIT VARYING in order to maintain compatibility with ODBC.

Internal ("physical") data types used by a client program correspond with the SQL types, when the client sends or reads data. The following table describes the correspondence between the two type sets. The third column contains the constants used for physical types description.

SQL Type Description	Internal Representation	Designation	Number
CHAR	one character	ATT_CHAR	2
CHAR(n), CHAR VARYING(n), VARCHAR(n), for n<=4090	string character up to length n	ATT_STRING	7
NCHAR(n), NCHAR VARYING(n), for n<=2045	Unicode character string up to length n	ATT_STRING	7
CLOB, CLOB(n)	character string of unlimited length	ATT_TEXT	20
NCLOB, NCLOB(n)	Unicode character string of unlimited length	ATT_TEXT	20
BLOB, BLOB(n)	order of bytes	ATT_NOSPEC	21
BINARY(n) for n<=4090	order of bytes up to length n	ATT_BINARY	10
BIT, BIT(1), BOOLEAN	boolean values TRUE, FALSE or UNKNOWN	ATT_BOOLEAN	1
BIT(n), BIT VARYING(n) for 1<n<=8*4090	order of (n-1) / 8 + 1 bytes	ATT_BINARY	10
INTEGER	4-byte integer number	ATT_INT32	4
SMALLINT	2-byte integer number	ATT_INT16	3
TINYINT	1-byte integer number	ATT_INT8	45
BIGINT	8-byte integer number	ATT_INT64	46
*DECIMAL(n, d), NUMERIC(n, d)* for n <= 2	1-byte number with a scale d	ATT_INT8	45
*DECIMAL(n, d), NUMERIC(n, d)* for 2 < n <= 4	2-byte number with a scale d	ATT_INT16	3
*DECIMAL(n, d), NUMERIC(n, d)* for 4 < n <= 9	4-byte number with a scale d	ATT_INT32	4
*DECIMAL(n, d), NUMERIC(n, d)* for n > 9	8-byte number with a scale d	ATT_INT64	46
REAL, DOUBLE PRECISION, FLOAT, FLOAT(n)	8-byte real number	ATT_FLOAT	6
DATE	date as a 4-byte number	ATT_DATE	11
TIME, TIME WITH TIME ZONE	time as a 4-byte number	ATT_TIME	12
TIMESTAMP, TIMESTAMP WITH TIME ZONE	date and time as a 4-byte number	ATT_TIMESTAMP	13

The NULL value belongs to each type. It's equivalent to the UNKNOWN value for BOOLEAN type, equivalent to the empty string for BLOB and string character types and equivalent to the string of zeros for binary strings. It's different from any other possible values for the other types.

Variances from Intermediate Level to the Entry Level

INTERVAL type not implemented, replaced by the INTEGER type to a certain extent.
The character string type may not contain the CHARACTER SET specification, the information about charset is stored in the language_and_charset clause.

Variances from Intermediate to the Full Level

You may also use the BIT and BIT VARYING type that belong to Full SQL. However these types are not a part of SQL2003. BIT or BIT(1) designates the BOOLEAN type. BIT(n), BIT VARYING(n) and VARBINARY(n) for n>1 designate the BINARY type of a length of (n-1)/8+1 bytes.
You may also use the CLOB, BLOB and BOOLEAN types that belong to SQL3.

SQL Properties in 602SQL Defined by Implementation

Character strings always have the behaviour of variable-length strings. If the fixed length is set, it's interpreted as the maximum length and the strings are not appended with blank spaces to the full length.
The TIME type has a precision of thousandths of a second, the TIMESTAMP type has a precision of seconds. You can set time with higher precision in literals, however that precision is not applied. The TIMESTAMP type recognizes dates from January 1st 1900 to August 18th 2033. Types that contain the WITH TIME ZONE clause are stored internally in UTC and contain local time and local time zone name when they're converted to character string.
The INTEGER type has a range -2147483647 to +2147483647, the SMALLINT type has a range -32767 to 32767, the TINYINT type has a range -127 to 127, the BIGINT type has a range -2^63-1 to 2^63-1 (c. +/-9223372036854775000).
The REAL, FLOAT and DOUBLE PRECISION type have a precision of 15 digits and a range 1e308 to 1e-308. The possible precision value is ignored.
The specified length value is not applied for BLOB, CLOB and NCLOB types.
The language and charset of (8bit) string types is distinguished by the COLLATE keyword.
The NCHAR and NCLOB types use the UCS-2 coding.

Operating System Requirements

Full support for Unicode and 8-bit strings in a non-Windows coding is available only in Windows 2000 and XP. The charset support depends on the system configuration for Linux systems - ISO charsets are usually supported, Windows code pages are usually not supported.

List of topics:

Lexical Elements of the SQL Language

SQL Language in 602SQL

Database Tables