Structured Query Language/Data Types

The SQL standard knows three kinds of data types
 * predefined data types
 * constructed types
 * user-defined types.

This page presents only the predefined data types. Constructed types are one of ARRAY, MULTISET, REF(erence) or ROW. User-defined types are comparable to classes in object-oriented language with their own constructors, observers, mutators, methods, inheritance, overloading, overwriting, interfaces, and so on.

Overview
The standard groups predefined data types into types with similar characteristics.


 * Character Types
 * Character (CHAR)
 * Character Varying (VARCHAR)
 * Character Large Object (CLOB)


 * Binary Types
 * Binary (BINARY)
 * Binary Varying (VARBINARY)
 * Binary Large Object (BLOB)


 * Numeric Types
 * Exact Numeric Types (NUMERIC, DECIMAL, SMALLINT, INTEGER, BIGINT)
 * Approximate Numeric Types (FLOAT, REAL, DOUBLE PRECISION)


 * Datetime Types (DATE, TIME, TIMESTAMP)
 * Interval Type (INTERVAL)
 * Boolean
 * XML
 * JSON (since SQL:2016)

Character types hold printable characters, binary types any binary data. Both may have a fixed or variable size with an upper limit. If the upper limit exceeds a certain value, the type is a 'large object' with special methods and functions.

Exact numeric types hold numeric values without digits after the decimal or with a firm number of digits after the decimal. Please note that the standard does not define a separate data type 'auto-increment' for generating primary keys. Instead it defines the phrase 'GENERATED ALWAYS AS IDENTITY' as part of the CREATE TABLE statement, see CREATE TABLE statement or auto-increment-columns.

Approximate numeric types hold numeric values with an implementation-defined precision (after the decimal).

Temporal types hold values for INTERVAL (a certain range on the time bar), DATE (year, month, day), TIME with and without TIMEZONE (name of timezone, hour, minute, second including fraction) and TIMESTAMP with and without TIMEZONE (name of timezone, year to second including fraction).

The boolean data type holds the two values true and false.

Part 14 of the SQL standard extends the list of predefined data types by introducing the data type XML (Oracle calls it XMLType) together with a bunch of particular functions. Columns of this type hold XML instances.

In the outdated SQL-2 standard, there was a data type 'BIT'. This data type is no longer part of the standard.

Most DBMS implement the majority of predefined data types, but there are some exceptions. Also, the naming differs slightly. An overview of the major implementations is available in the Wikibook SQL_Dialects_Reference.

Data types are used within the CREATE TABLE statement as part of column definitions - or during CAST operations.

Character
A series of printable characters - which is a string - can be stored within character string types. If all rows of a table use the same fixed size for the strings, the data type is  where  is the size of the strings. If the size varies from row to row, the data type  defines that up to   characters can be stored in the column. So  defines the upper limit for this column. The maximum value for  depends on the used DBMS implementation. If applications need to store longer strings than it is allowed by this upper system limit, the data type  must be used. Also  has its own upper limit, but this is significantly greater than the upper limit of.

Hint: Unlike other programming languages, SQL does not distinguish between a character data type and a string data type. It knows only the character string data types CHAR, VARCHAR and CLOB.

Binary
Binary data types are similar to character data types. They differ in that they accept a different range of bytes. Binary data types accept all values.

A hint to Oracle users: The data type  is not supported, the data type   is denoted as   and is deprecated. Oracle recommends the use of.

Exact Numeric
Exact numeric types hold numeric values without digits after the decimal or with a firm number of digits after the decimal. All exact numeric types are signed.

and  denotes two types which are nearly the same. &lt;p> (precision) defines a fixed number of all digits within the type and &lt;s> (scale) defines how many of those digits follow the decimal place. Numeric values with more than (p - s) digits before the decimal place cannot be stored and numeric values with more than s digits after the decimal place are truncated to s digits after the decimal place. p and s are optional. It must always be: p ≥ s ≥ 0 and p > 0.

SMALLINT, INTEGER and BIGINT denote data types without a decimal place. The SQL standard did not define their size, but the size of SMALLINT shall be smaller than the size of INTEGER and the size of INTEGER shall be smaller than the size of BIGINT.

Approximate Numeric
Approximate numeric types hold numeric values with an implementation-defined precision (after the decimal). All approximate numeric types are signed. Their primary use cases are scientific computations.

There are three types:,   and  , where p denotes the guaranteed precision of the   data type. The precision of  and   is implementation defined.

Temporal
Data types with respect to temporal aspects are:  and.

stores year, month and day. stores hour, minute and second. stores year, month, day, hour, minute and second. Seconds can contain digits after the decimal. and  can contain the name of a TIME ZONE.

The SQL standard defines two kinds of INTERVALs. The first one is an interval with year and month, the second one is an interval with day, hour, minute, and second.

A hint to Oracle users: The data type  is not supported. Use  instead.

A hint to MySQL users: The use of TIME ZONE as part of data types is not supported. MySQL implements a different concept to handle time zones. Fractions of seconds are not supported. The data type  is not supported, but there is a data value.

Boolean
SQL has a 3-value-logic. It knows the boolean values,   and. Columns of the boolean data type can store one of the two values  or. is represented by storing no value, which is the NULL indicator.

XML
Part 14 of the SQL standard extends the list of predefined data types by introducing the data type XML. The standard also defines a wide range of particular functions for this data type.

A hint to Oracle users: The data type  is denoted as.

A hint to MySQL users: The data type  is not supported.

Domains
In the context of data types, the standard knows domains. The purpose of domains is to constrain the set of valid values that can be stored in a column. The domain-concept is a very early predecessor of user-defined types and may be outdated.

Exercises
Create a table 'company' with columns 'id' (numeric, primary key), 'name' (strings of variable size up to 200), 'isin' (strings of length 12), 'stock_price' (numeric with 2 digits before and 2 after the decimal), 'description_text' (a very large string) and description_doc (any binary format).