SQL Data Types Explained

Concept

A column type is a constraint you get for free. It decides what can be stored, how values sort, how much space a row uses and which functions apply. Choosing types carelessly is the cheapest way to create years of data cleaning work.

Numeric types

String types

Date, time and boolean

Syntax

CREATE TABLE orders (
    id           BIGINT        NOT NULL,
    customer_id  INT           NOT NULL,
    order_date   DATE          NOT NULL,
    placed_at    TIMESTAMP     NOT NULL,
    status       VARCHAR(12)   NOT NULL,
    total        DECIMAL(10,2) NOT NULL,
    is_gift      BOOLEAN       NOT NULL DEFAULT FALSE,
    note         TEXT          NULL
);

Example

Why approximate types are wrong for money:

-- Exact
SELECT CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2)) AS exact_sum;
-- 0.30

-- Approximate, in every language that uses binary floating point
SELECT 0.1E0 + 0.2E0 AS float_sum;
-- 0.30000000000000004

Explanation

DECIMAL stores digits, so 0.1 is exactly 0.1. FLOAT stores a binary approximation, and the tiny error compounds across a million invoice lines until a reconciliation report is off by a few paise. Storage is cheap; a failed audit is not.

Important rules

• Use DECIMAL for money, always.
• Store dates in date types, never as strings. '31/01/2024' sorts before '01/02/2023' as text.
• Size VARCHAR to a real business limit, not to the biggest number you can imagine.
• Store timestamps in UTC and convert at the edge, unless the business genuinely needs local wall clock time.

Common mistakes

• VARCHAR for phone numbers is correct; INT for phone numbers is not - leading zeros and plus signs disappear.
• Using FLOAT for prices and then chasing rounding differences.
• Declaring everything VARCHAR(255) out of habit.
• Mixing DATETIME and TIMESTAMP in MySQL without knowing that only TIMESTAMP converts by session time zone.

Best practices

• Pick the narrowest type that comfortably fits the domain, then leave headroom on ids.
• Keep the same type for the same concept across tables, so joins never need a cast.
• Prefer CHECK constraints or lookup tables to a free text status column.

Practice

1. Choose a type for each: invoice total, ISO country code, product description, order placed instant, number of items in stock, flag for subscribed to newsletter.
2. Explain why employees.dept_id and departments.id should share a type.
3. What breaks if a birth date is stored as VARCHAR(10)? Name two queries that become hard.