About
ANSI
They are defined in the ANSI/ISO SQL standard 1) but may vary from sql engine (database) to another.
The below table shows you the history of the data type in the ANSI standard.
| Standard | built-in types |
|---|---|
| SQL92 | CHARACTER, CHARACTER VARYING, BIT, BIT VARYING, NUMERIC, DECIMAL, INTEGER, SMALLINT, FLOAT, REAL, DOUBLE PRECISION, DATE, TIME, TIMESTAMP, and INTERVAL. See Sql92 doc |
| SQL99 | BOOLEAN: Boolean (true or false) value BLOB: Binary large object CLOB: Character large object |
| SQL:2003 | XML: XML object |
Type
ANSI
The ansi data type of the database:
| Sql Type | Description (Range) | Precision | Jdbc Sql Type Constant |
|---|---|---|---|
| Fixed point Number (exact numeric) | |||
| NUMERIC | fixed-point with precision and scale | (up to 131072) and scale (up to 16383) | NUMERIC |
| DECIMAL | alias for numeric | DECIMAL | |
| Integer (exact numeric, with binary or decimal precision and scale of 0) | |||
| TINYINT | Tiny Integer | 0 to 255 (1 Bytes) | TINYINT |
| SMALLINT | Small Integer | ±32768 (2 Bytes) | SMALLINT |
| INTEGER | Integer | ±2147483648 (4 Bytes) | INTEGER |
| BIGINT | Big Integer | ±9223372036854775808 (8 Bytes) | BIGINT |
| Floating point Number (approximate numeric) | |||
| FLOAT | precision may defined | Single precision | FLOAT |
| REAL | precision is fixed | Single precision (4 Bytes) | REAL |
| DOUBLE PRECISION | precision greater than REAL | Double Precision | DOUBLE |
| Text (String) | |||
| CHAR | text padded with space | CHAR | |
| NCHAR | text padded with space with character set | CHAR | |
| VARCHAR | variable text | VARCHAR | |
| NVARCHAR | variable text with character set | NVARCHAR | |
| CLOB | large text stored in a file | CLOB | |
| Time | |||
| DATE | Time at the day level | DATE | |
| TIMESTAMP | Time at the ms level | TIMESTAMP | |
| TIME | time part (hh:mm:ss) | TIME | |
| INTERVAL | INTERVAL | ||
| Others | |||
| BLOB | binary large object (binary data) | ||
| IDENTIFIER | |||
| STRUCT | |||
User defined
Data Type may be defined/changed. This section shows some example of user defined data type.
Structured type
CREATE TYPE PLANE_POINT
AS (X FLOAT, Y FLOAT) NOT FINAL
DISTINCT type
User-defined type based on a built-in type; for example:
CREATE TYPE MONEY
AS NUMERIC(10,2) FINAL
Constructed types
A database may offers types based on a given base type such as:
- base-type ARRAY[n]: Array of n base-type elements
Locators / Pointer / Reference
They are pointer to the location of data known also as REF(structured-type)
A locator typically refers to data that is too large to be included in the table such as images or audio.
Datalink
A datalink is a type that creates a link with another database. Datalink values are part of the SQL ANSI/ISO standard specification (and of SQL MED - Management of External Data)