SQL - Data Type (Column Type)

About

The Data Type of a column in SQL.

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.

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)


Powered by ComboStrap