SQL Data Types

A data type defines what kind of values a column can hold, how much storage it uses, and which operations are valid.

Concept

A data type defines what kind of values a column can hold, how much storage it uses, and which operations are valid. Choosing correct types improves integrity (reject invalid values), storage efficiency, and performance (smaller rows, faster comparisons, better indexes).

Why It Matters

Storing money in FLOAT causes rounding errors in financial reports.

Storing dates as strings breaks sorting ('10/01/2026' < '9/01/2026' as text) and date arithmetic.

Oversized types waste memory in indexes and caches.

Type mismatches in joins/filters can prevent index usage (implicit conversion).

Syntax

column_name DATA_TYPE[(length | precision, scale)] [constraints]

Example

4.1 Numeric Types Range / Precision −32,768 to 32,767 Small counts, years, status codes INT / INTEGER about ±2.1 billion Most IDs and counts about ±9.2 × 10^18

High-volume IDs, event logs, CAN frame counters DECIMAL(p,s) / NUMERIC(p,s)

Exact; p total digits, s after decimal point Money, measurements that must be exact ~6–7 significant digits (approximate)

Sensor values where approximation is acceptable DOUBLE PRECISION / DOUBLE ~15–16 significant digits (approximate) Scientific/engineering calculations

DECIMAL(10,2) stores up to 99,999,999.99 exactly. DECIMAL and NUMERIC are functionally equivalent in most systems.

MySQL: TINYINT (1 byte), MEDIUMINT (3 bytes); UNSIGNED variants double the positive range. DOUBLE is MySQL's name; PostgreSQL uses DOUBLE PRECISION.

SQL Server: TINYINT (0–255), MONEY (fixed 4 decimals; many teams prefer DECIMAL for portability).

Oracle: NUMBER(p,s) covers integers and decimals; BINARY_FLOAT, BINARY_DOUBLE for floating point.

SQLite uses type affinity: it stores values as INTEGER, REAL, TEXT, BLOB, or NULL, and declared types are hints (unless the table is declared STRICT, SQLite 3.37+).

Why never use FLOAT for money:

4.2 Character Types Fixed length; padded with spaces to n Fixed-width codes: country CHAR(2), currency CHAR(3) Variable length up to n Names, emails, most text

Long variable text (PostgreSQL: unlimited; MySQL: up to 64 KB, with MEDIUMTEXT/LONGTEXT for more) Descriptions, comments, logs

Fixed-length Unicode (national character) SQL Server/Oracle Unicode fixed codes

SQL Server/Oracle multilingual text (Hindi, Chinese, etc.) In PostgreSQL and MySQL (with utf8mb4), VARCHAR already stores Unicode; NVARCHAR is mainly relevant to SQL Server and Oracle. In SQL Server, VARCHAR uses the database code page unless a UTF-8 collation is used (2019+), so use NVARCHAR for international text; Unicode literals are written N'text'.

Oracle uses VARCHAR2(n); VARCHAR is reserved for future semantic changes.

SQL Server: VARCHAR(MAX)/NVARCHAR(MAX) for large text; TEXT/NTEXT are deprecated.

In PostgreSQL, VARCHAR(n), VARCHAR, and TEXT perform the same; use VARCHAR(n) when you want a length rule.

4.3 Date and Time Types Oracle DATE also contains time (to seconds)

MySQL, SQL Server (DATETIME2 preferred in SQL Server) Date + time (optionally with fractional seconds) See time-zone notes below TIMESTAMP WITH TIME ZONE Instant in time

PostgreSQL TIMESTAMPTZ, Oracle; SQL Server DATETIMEOFFSET INTERVAL '3 days'

PostgreSQL, Oracle (standard SQL); not a column type in MySQL/SQL Server Database notes:

PostgreSQL: TIMESTAMP (no zone) and TIMESTAMPTZ (stored as UTC, displayed in session time zone). Prefer TIMESTAMPTZ for events.

MySQL: DATETIME (no conversion, range 1000–9999) vs TIMESTAMP (converted to UTC for storage and back to session time zone; range 1970–2038).

SQL Server: DATE, TIME, DATETIME2 (recommended), DATETIMEOFFSET; SQL Server's TIMESTAMP/ROWVERSION is not a date — it is a row version number.

SQLite: no dedicated date type; store ISO-8601 text ('2026-09-29 14:30:00'), Julian day REAL, or Unix epoch INTEGER, and use date functions.

Always use ISO format literals: 'YYYY-MM-DD' and 'YYYY-MM-DD HH:MM:SS'.

4.4 Boolean Types TRUE, FALSE, NULL BOOLEAN (alias for TINYINT(1)) 1/0 (TRUE/FALSE are aliases) 1, 0, NULL

BOOLEAN in 23ai+; earlier use NUMBER(1) or CHAR(1) with CHECK

No boolean; stored as integer 1/0 (keywords TRUE/FALSE accepted since 3.23) 4.5 Binary Types

Hashes (SHA-256 = 32 bytes), fixed binary IDs Small binary payloads, CAN frame data (up to 8 bytes classic CAN, 64 bytes CAN FD) Large binary object

Images, firmware files, PDFs (MySQL, Oracle, SQLite) PostgreSQL binary type Any binary data SQL Server large binary

Storing large files in the database is possible, but many systems store files in object storage (S3, file server) and keep only the path/URL and checksum in the database.

4.6 Other Database-Specific Types MySQL 5.7+, PostgreSQL (JSON, JSONB), Oracle 21c+, SQLite (JSON functions on TEXT), SQL Server (NVARCHAR + JSON functions; native json type in 2025/Azure)

Semi-structured attributes, API payloads SQL Server, PostgreSQL, Oracle (XMLTYPE), Db2

XML documents (AUTOSAR ARXML, legacy integrations) <ecu id="BCM"/>

PostgreSQL UUID; SQL Server UNIQUEIDENTIFIER; MySQL BINARY(16) or CHAR(36) Globally unique IDs

MySQL (inline), PostgreSQL (CREATE TYPE... AS ENUM) Small fixed set of values PostgreSQL (INT[], TEXT[])

Lists of values when normalization is unnecessary 4.7 Choosing the Right Type — Decision Guide INT / BIGINT identity, or UUID VARCHAR IDs without reason DECIMAL(12,2) / NUMERIC(19,4) FLOAT, REAL FLOAT (if exact needed) Quantity / count Temperature, voltage reading REAL/DOUBLE or DECIMAL(8,3) VARCHAR(n) / NVARCHAR(n) CHAR(n) (padding) Country / currency code CHAR(2) / CHAR(3) Phone number, PIN code, VIN

VARCHAR (they are identifiers, not numbers; leading zeros matter) VARCHAR, DATETIME

TIMESTAMPTZ / DATETIMEOFFSET / UTC DATETIME Local time without zone BOOLEAN / BIT VARCHAR('Y'/'N') if boolean available Status with few values Lookup table or CHECK JSON/JSONB column Dozens of sparse nullable columns

SELECT CAST(0.1 AS DOUBLE PRECISION) + CAST(0.2 AS DOUBLE PRECISION) AS float_sum,
       CAST(0.1 AS DECIMAL(5,2))     + CAST(0.2 AS DECIMAL(5,2))     AS decimal_sum;

CREATE TABLE countries (
    country_code  CHAR(2)       PRIMARY KEY,   -- 'IN', 'US'
    country_name  VARCHAR(100)  NOT NULL,
    description   TEXT
);

-- PostgreSQL
CREATE TABLE feature_flags (flag_name VARCHAR(50) PRIMARY KEY, enabled BOOLEAN NOT NULL DEFAULT FALSE);

-- SQL Server
CREATE TABLE feature_flags (flag_name VARCHAR(50) PRIMARY KEY, enabled BIT NOT NULL DEFAULT 0);

-- PostgreSQL JSONB
CREATE TABLE product_attributes (
    product_id  INT PRIMARY KEY REFERENCES products(product_id),
    attrs       JSONB NOT NULL
);
INSERT INTO product_attributes VALUES (101, '{"ram_gb": 16, "cpu": "8-core", "ports": ["usb-c", "hdmi"]}');
SELECT attrs->>'cpu' AS cpu FROM product_attributes WHERE (attrs->>'ram_gb')::INT >= 16;

-- MySQL JSON
SELECT JSON_UNQUOTE(JSON_EXTRACT(attrs, '$.cpu')) AS cpu FROM product_attributes;   -- or attrs->>'$.cpu'

-- MySQL ENUM
CREATE TABLE shipments (
    shipment_id INT PRIMARY KEY,
    status ENUM('PENDING', 'SHIPPED', 'DELIVERED') NOT NULL DEFAULT 'PENDING'
);

-- PostgreSQL ENUM
CREATE TYPE shipment_status AS ENUM ('PENDING', 'SHIPPED', 'DELIVERED');
CREATE TABLE shipments (
    shipment_id INT PRIMARY KEY,
    status shipment_status NOT NULL DEFAULT 'PENDING'
);

-- Portable alternative to ENUM: CHECK constraint or lookup table
CREATE TABLE shipments (
    shipment_id INT PRIMARY KEY,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING'
        CHECK (status IN ('PENDING', 'SHIPPED', 'DELIVERED'))
);

-- PostgreSQL ARRAY
CREATE TABLE ecus (ecu_id INT PRIMARY KEY, buses TEXT[]);
INSERT INTO ecus VALUES (1, ARRAY['CAN', 'LIN'])
…

Expected Result

('abc' shows unknown because an untyped string literal has no type until context assigns one.)

-- PostgreSQL
SELECT pg_typeof(12), pg_typeof(12.5), pg_typeof('abc'), pg_typeof(DATE '2026-09-29');

Common Mistakes

Using FLOAT for currency.

Storing numbers or dates as strings.

Storing phone numbers as INT (leading zeros lost, + sign impossible).

Using VARCHAR(255) for everything by habit.

Using CHAR(n) for variable data and being surprised by trailing spaces.

Using SQL Server VARCHAR for multilingual text instead of NVARCHAR.

Assuming SQL Server TIMESTAMP is a date/time type (it is a row version).

Mixing time zones: storing some timestamps in local time and some in UTC.

Overusing JSON for data that has a stable structure and needs joins and constraints.

Best Practices

Use the smallest type that safely fits current and future data (INT vs BIGINT: if a table may exceed 2 billion rows, use BIGINT from the start).

Use DECIMAL for money; store amounts plus a currency code.

Store timestamps in UTC (or with time zone) and convert at display time.

Use the same data type for a foreign key and the primary key it references.

Prefer lookup tables or CHECK constraints over free-text status columns.

Interview Relevance

High: "CHAR vs VARCHAR?", "VARCHAR vs NVARCHAR?", "DECIMAL vs FLOAT?", "DATETIME vs TIMESTAMP in MySQL?", "How would you store money?", "When would you use JSON in a relational DB?".

Practice Questions

Choose types for: IFSC code, account balance, date of birth, profile photo, last login time, number of logins, VIN, engine temperature.

Why is CHAR(10) a poor choice for a first name?

Write a PostgreSQL table sensor_readings with a big-integer ID, a sensor code, a timestamp with time zone, and a numeric value with 3 decimal places.

Explain why DECIMAL(5,2) cannot store 1234.5.

What is the portable alternative to MySQL ENUM?