SQL: Data Types
Master numeric, string, temporal, boolean, binary, and JSON types, understand three-valued NULL logic, and inspect database dialect variations.
1. SQL Data Types Overview
Every column in a relational database table must have a declared data type. When you define a table with CREATE TABLE, you instruct the database engine on what category of data that column can accept, how many bytes of physical storage to allocate per row, and which operations can be executed on those values.
Unlike loosely typed scripting languages where a variable can hold a number on one line and a string on the next, relational database engines enforce strict type contracts. If a column is defined as an integer, the database will reject any query that attempts to write alphabetical text into that slot.
Four Responsibilities of Data Types
Assigning the correct data type to each column guarantees four system-level behaviors in relational engines:
- Storage Allocation:Determines whether a value occupies 1 byte, 4 bytes, or variable heap space on disk pages.
- Data Integrity:Rejects invalid values at write time, preventing corrupted records from entering production tables.
- Operator Semantics:Dictates whether the plus symbol performs mathematical addition or string concatenation.
- Index Efficiency:Enables the cost-based query optimizer to pack index pages tightly and accelerate lookups.
2. Numeric Data Types: INT, BIGINT, SMALLINT, DECIMAL, NUMERIC, FLOAT
SQL divides numbers into two primary categories: exact numbers (integers and fixed-point decimals) and approximate numbers (floating-point representations). Selecting the correct numeric category prevents rounding errors and saves disk storage across millions of rows.
| Data Type | Storage | Signed Range | Standard Use Case |
|---|---|---|---|
| TINYINT | 1 Byte | -128 to 127 (or 0 to 255 unsigned) | Status codes, age, small category counters |
| SMALLINT | 2 Bytes | -32,768 to 32,767 | Years, employee count in departments, small lists |
| INT / INTEGER | 4 Bytes | -2,147,483,648 to 2,147,483,647 | Standard primary keys, user IDs, quantities |
| BIGINT | 8 Bytes | -9.22 x 10^18 to 9.22 x 10^18 | High-throughput event logs, analytics, financial ledgers |
| DECIMAL(M, D) | Variable | Exact precision up to M digits total, D after point | Currency, invoice totals, accounting balances |
| FLOAT / REAL | 4 or 8 Bytes | Approximate IEEE 754 floating-point values | Scientific sensor readings, geometry coordinates |
The Financial Rule: DECIMAL vs FLOAT
Never store monetary amounts, item prices, or account balances in FLOAT or REAL columns.
- Why FLOAT Fails for Money:Floating-point types store binary approximations. Adding 0.1 and 0.2 can result in 0.30000000000000004, causing accounting audit discrepancies.
- Why DECIMAL Is Exact:DECIMAL(10, 2) stores numbers as base-10 representations, ensuring exact penny and cent precision without approximation rounding drift.
- Syntax Breakdown:In DECIMAL(10, 2), 10 is the precision (total digits), and 2 is the scale (digits right of the decimal point), allowing values up to 99,999,999.99.
Numeric Calculations in SQL
SQLObserve how SQL evaluates decimal numbers and casts between numeric types
3. String Data Types: CHAR, VARCHAR, TEXT
SQL provides three foundational character data types to store textual data. Choosing among them depends on whether the length of the string is fixed, variable, or unbounded:
| String Type | Length Behavior | Maximum Capacity | Common Applications |
|---|---|---|---|
| CHAR(n) | Fixed length | Up to 255 characters | Two-letter country codes, currency codes, fixed hash digests |
| VARCHAR(n) | Variable length | Up to 65,535 bytes (engine dependent) | Customer names, email addresses, street names, URLs |
| TEXT | Unlimited / Large Object | Up to 1 GB or 4 GB (PostgreSQL / MySQL) | Article body content, product descriptions, error log traces |
Character Encoding and Byte Sizes
Modern databases default to UTF-8 encoding (such as utf8mb4 in MySQL), where one character can consume between 1 and 4 bytes:
- ASCII Characters:Standard English letters (A-Z, 0-9) consume exactly 1 byte each.
- Accented & Non-Latin:Letters like e with accent or Arabic script take 2 bytes each.
- Asian Characters & Symbols:Chinese, Japanese, and emoji characters require 3 or 4 bytes per character.
- VARCHAR Limit Rule:A column defined as VARCHAR(50) holds up to 50 characters, regardless of how many bytes each individual character requires.
4. CHAR vs VARCHAR (Deep Dive)
Understanding the internal storage mechanics of CHAR and VARCHAR explains why both types exist side by side in SQL:
CHAR(n): Fixed Storage With Space Padding
When you store text in a CHAR(10) column, the database always reserves exactly 10 characters on disk.
- Storage Behavior:Storing the 3-letter word 'SQL' in CHAR(10) causes the engine to append 7 spaces: 'SQL '.
- Read Behavior:When querying CHAR columns, the database automatically removes trailing padding spaces before returning values.
- Performance Benefit:Because row width is completely static, the engine accesses records on disk pages without reading length prefix bytes.
VARCHAR(n): Variable Storage With Length Prefix
When you store text in a VARCHAR(10) column, the database stores only the characters provided plus a small length header.
- Storage Behavior:Storing 'SQL' in VARCHAR(10) stores exactly 3 characters plus a 1-byte length prefix (storing the number 3).
- Disk Savings:Short strings consume minimal disk space, preventing thousands of wasted padding bytes across large tables.
- Overhead:Each row requires an extra 1 to 2 bytes for the length prefix overhead.
Decision Guide: When to Pick Which
Follow this clear rule of thumb when designing relational schemas:
- Use CHAR for Fixed-Length Codes:Country codes ('US', 'GB'), state codes ('CA', 'NY'), currency codes ('USD', 'EUR'), and UUIDs.
- Use VARCHAR for Unpredictable Lengths:Names, emails, physical addresses, job titles, and passwords.
String Functions in SQL
SQLTest string length calculations and uppercase conversions against student records
5. Date and Time Data Types: DATE, TIME, DATETIME, TIMESTAMP
Temporal data types record chronological events, scheduling deadlines, and audit history. Storing dates as strings (such as VARCHAR) makes date arithmetic, range filtering, and chronological sorting slow and error-prone.
| Temporal Type | Format Example | Storage | Timezone Sensitive |
|---|---|---|---|
| DATE | 2026-10-09 | 3 Bytes | No (stores date only) |
| TIME | 14:30:00 | 3 to 5 Bytes | No (stores time of day only) |
| DATETIME | 2026-10-09 14:30:00 | 5 to 8 Bytes | No (literal wall-clock value) |
| TIMESTAMP | 2026-10-09 09:30:00 UTC | 4 to 8 Bytes | Yes (converts to UTC for storage) |
DATETIME vs TIMESTAMP: When to Use Each
The critical difference between DATETIME and TIMESTAMP lies in timezone conversion:
- DATETIME (Wall-Clock Time):Stores the exact date and hour you write without converting timezone offsets. Ideal for future appointments like a conference starting at 09:00 AM local venue time.
- TIMESTAMP (Global Instant):Converts current local time to UTC on insert, and converts back to the viewing client timezone on retrieval. Ideal for audit columns like created_at and updated_at.
- ISO 8601 Standard:Always write date literals using the international standard YYYY-MM-DD format to prevent regional ambiguity between American (MM/DD) and European (DD/MM) conventions.
Temporal Functions in SQL
SQLRetrieve the current UTC date, time, and year using native SQL functions
6. BOOLEAN Data Type
The SQL standard defines the BOOLEAN type to store truth values. In relational theory, booleans can hold three states: TRUE, FALSE, or NULL (representing an unknown state).
Vendor Implementation Differences
Database systems implement boolean storage using different underlying physical representations:
- PostgreSQL:Provides a true native BOOLEAN type that accepts TRUE, FALSE, and NULL (occupies 1 byte).
- MySQL / MariaDB:BOOLEAN is a synonym for TINYINT(1), where 1 represents TRUE and 0 represents FALSE.
- SQLite:Has no separate boolean class; uses integers 0 (false) and 1 (true).
- SQL Server:Uses the BIT type, storing 1, 0, or NULL.
- Writing Safe Queries:To write portable SQL across all engines, write conditions as WHERE is_active = 1 or WHERE is_active = TRUE.
7. BLOB and Binary Data Types
Binary data types store raw bytes without character set encoding or collation rules. SQL provides BINARY(n) (fixed-length bytes), VARBINARY(n) (variable-length bytes), and BLOB (Binary Large Object, called BYTEA in PostgreSQL).
Architectural Principle: The Database Is Not a File System
While you can technically store high-resolution images, videos, and PDFs inside BLOB columns, doing so in production is an architectural anti-pattern.
- Buffer Pool Bloat:Loading massive binary blobs into database RAM flushes hot index pages and cache tables out of memory.
- Backup Degradation:Database dumps, WAL log replication, and point-in-time recovery times expand from minutes to hours.
- Industry Standard Pattern:Store media files in cloud object storage (Amazon S3, Google Cloud Storage, or MinIO) and store only the resulting public URL string (VARCHAR) inside the database column.
- Valid BLOB Use Cases:Cryptographic public keys, password salt hashes, compiled protocol buffer payloads, and small binary tokens.
8. JSON Data Type
Modern relational databases combine the structural integrity of tables with the flexibility of document stores by offering native JSON data types. This allows applications to store semi-structured nested documents without requiring external NoSQL databases.
Plain JSON vs Binary JSONB
Enterprise databases (PostgreSQL and MySQL 8+) differentiate between plain text JSON and decomposed binary JSONB:
- JSON (Text):Stores the exact original JSON text including whitespace and key ordering. Validates JSON syntax on insert, but reparses text on every read query.
- JSONB (Binary):Decomposes the JSON object into a binary format on insert, removing whitespace and duplicate keys. Reads and key lookups are dramatically faster.
- Indexing Capabilities:JSONB columns support GIN (Generalized Inverted Index) indexing, allowing instant searches into nested sub-properties.
Querying JSON in SQL
SQLExtract nested keys and array elements using standard json_extract path expressions
9. NULL Values in SQL
One of the most misunderstood concepts in SQL is the NULL value. In relational algebra, NULL does not equal zero (0), and it does not equal an empty string (''). NULL signifies the complete absence of a value or an unknown state.
Three-Valued Logic (3VL) & The '= NULL' Trap
Standard boolean logic has two states (TRUE and FALSE). SQL uses Three-Valued Logic with three states: TRUE, FALSE, and UNKNOWN.
- The Critical Trap:Writing WHERE status = NULL evaluates to UNKNOWN, which the database treats as false. Zero rows will ever be returned!
- Correct Comparison Syntax:Always write WHERE status IS NULL or WHERE status IS NOT NULL.
- NULL Propagation in Math:Any mathematical operation with NULL results in NULL: 10 + NULL equals NULL.
- COALESCE Function:Use COALESCE(column_name, default_value) to replace NULL entries with fallback values during calculation.
Testing NULL Values & COALESCE
SQLObserve how IS NULL detects missing grades and COALESCE provides fallback values
10. Data Type Differences Between Databases
While ANSI SQL specifies standard data types, major database engines diverge in their implementations, auto-increment keywords, and storage features:
| Feature / Type | PostgreSQL | MySQL | SQLite | SQL Server |
|---|---|---|---|---|
| Auto-Increment Key | SERIAL / GENERATED AS IDENTITY | AUTO_INCREMENT | INTEGER PRIMARY KEY | IDENTITY(1,1) |
| Boolean Support | Native BOOLEAN | TINYINT(1) alias | INTEGER (0 or 1) | BIT |
| JSON Support | JSON and indexed JSONB | Native binary JSON | JSON1 extension functions | NVARCHAR with JSON functions |
| Long Text | TEXT (unlimited) | TEXT, MEDIUMTEXT, LONGTEXT | TEXT (unlimited) | VARCHAR(MAX) |
| Binary Data | BYTEA | BLOB, LONGBLOB | BLOB | VARBINARY(MAX) |
SQLite Type Affinity: A Unique Model
Unlike PostgreSQL or MySQL where columns strictly reject mismatched types, SQLite uses dynamic type affinity:
- Type Affinity Classes:Every column has one of five affinities: TEXT, NUMERIC, INTEGER, REAL, or BLOB.
- Coercion Behavior:When inserting data, SQLite attempts to convert the value into the column preferred affinity. If conversion is impossible, SQLite stores the value as-is without throwing an error.
- Migration Warning:Code written for SQLite that relies on loose typing will fail immediately when deployed to PostgreSQL or MySQL production databases.
Knowledge Check: SQL Data Types
1. Which data type should you choose to store monetary currency values?
2. What is the primary difference between CHAR(10) and VARCHAR(10)?
3. How must you test whether a column contains a missing value?
4. What is the result of the SQL arithmetic expression: 25 + NULL?
5. How does TIMESTAMP differ from DATETIME in relational databases?
6. What type storage model does SQLite use instead of rigid column typing?