ANSI SQL types

ANSI SQL defines a standard set of data types that serve as the foundation for relational database management systems. These types provide a consistent structure for storing and manipulating data across different database implementations.

Numeric Types

ANSI SQL defines numeric data types into two main categories: exact and approximate.

Exact Numeric Types

Exact numeric types ensure precise representation, suitable for storing values that require high accuracy, such as monetary values.

SMALLINT: A 16-bit integer, storing values from -32,768 to 32,767.

INTEGER (INT): A 32-bit integer, storing values from -2,147,483,648 to 2,147,483,647.

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    age INT NOT NULL
);

BIGINT: A 64-bit integer, storing values from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
DECIMAL(p, s) / NUMERIC(p, s): Stores fixed-point decimal numbers with p total digits and s decimal places.
```
CREATE TABLE products (
    price DECIMAL(10,2)
);
```
DECIMAL and NUMERIC behave identically in ANSI SQL, ensuring no loss of precision. Unlike floating-point types, these are exact and suitable for financial calculations.

Approximate Numeric Types

These types store floating-point numbers and are suitable for scientific calculations where precision can be approximated.

REAL: A 32-bit single-precision floating-point number.
FLOAT(p): A floating-point number with at least p digits of precision, typically mapped to either REAL or DOUBLE PRECISION depending on p.
DOUBLE PRECISION: A 64-bit double-precision floating-point number.

CREATE TABLE measurements (
    temperature FLOAT(6),
    pressure DOUBLE PRECISION
);

Character String Types

Character string types store textual data.

Fixed-length and Variable-length Strings

CHAR(n): Fixed-length character storage, right-padded with spaces if the value is shorter than n.
VARCHAR(n): Variable-length string storage, up to n characters.
```
CREATE TABLE users (
    username VARCHAR(50),
    country CHAR(2)
);
```
VARCHAR allows efficient storage as it only occupies the required space, whereas CHAR is faster for fixed-size values.

Large Text Storage

CLOB (Character Large Object): Stores large text data, such as documents, with a maximum size often in the range of several gigabytes, depending on the implementation.
```
CREATE TABLE articles (
    content CLOB
);
```
CLOBs are optimized for large storage, often stored separately from the table rows.

Binary Data Types

Binary types store raw binary data such as images or encrypted values.

BLOB (Binary Large Object): Stores large binary objects. The maximum size depends on the database system but typically ranges from megabytes to terabytes.
BINARY(n): Fixed-length binary data.
VARBINARY(n): Variable-length binary data.
```
CREATE TABLE files (
    file_data BLOB
);
```
Binary types differ from textual types as they do not interpret character encodings.

Temporal (Date/Time) Types

Temporal types store dates, times, and timestamps.

DATE: Stores only date values (YYYY-MM-DD).
TIME: Stores only time values (HH:MI:SS).
TIMESTAMP: Stores both date and time values, typically with fractional seconds precision.
INTERVAL: Represents time durations.
```
CREATE TABLE events (
    event_date DATE,
    start_time TIME,
    event_timestamp TIMESTAMP
);
```
TIMESTAMP precision varies by database, often supporting fractional seconds (e.g., TIMESTAMP(3) for milliseconds).

Boolean Type

ANSI SQL includes BOOLEAN, which stores TRUE, FALSE, or UNKNOWN.

CREATE TABLE settings (
    is_enabled BOOLEAN
);

Some databases may use BIT(1) instead of BOOLEAN, as early versions of SQL did not specify a dedicated boolean type.

Special Data Types

Important

XML and JSON are not a part of ANSI types, but they are widely supported in many modern databases

Edmondo's Vault

Explorer