SQL Data Types
Every column in every table must have a data type — it tells the database exactly what kind of value it will store, how much disk space to reserve, and what operations are valid on that column. Choosing the wrong type wastes storage, breaks calculations, or silently truncates your data. In this chapter you'll master every SQL data type — numeric, string, date/time, boolean, binary, ENUM, and JSON — with real examples and a practical decision guide.
SQL Data Types — Overview
SQL groups its data types into six broad categories. Each category is optimised for a specific kind of information. Here's the big picture before we dive into each one:
FLOAT · DOUBLE · TINYINT
MEDIUMTEXT · LONGTEXT
TIMESTAMP · YEAR
ENUM · SET
BINARY · VARBINARY
Numeric Data Types
Numeric types store numbers — integers (whole numbers), exact decimals, and floating-point approximations. The key decision is whether you need exact precision (use DECIMAL) or just approximate values (FLOAT/DOUBLE), and how large your numbers can get.
TINYINT(1) is MySQL's internal
representation of BOOLEAN.UNSIGNED: 0 to 4,294,967,295
AUTO_INCREMENT for primary keys.e.g. DECIMAL(10,2) → 99999999.99
NUMERIC(p,s).0.1 + 0.2 ≠ 0.3.DOUBLE PRECISION, REAL.CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
stock_qty SMALLINT UNSIGNED NOT NULL DEFAULT 0,
weight_kg DECIMAL(8, 3), -- e.g. 1234.567 kg (exact)
price DECIMAL(10, 2) NOT NULL, -- e.g. 99999999.99 (exact ✅)
rating TINYINT UNSIGNED CHECK(rating BETWEEN 1 AND 5),
view_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
temperature FLOAT -- sensor reading (approx ok)
);
String (Text) Data Types
String types store any character-based data — names, emails, addresses, articles, HTML content. The main decision is between fixed-length vs variable-length and how long your values can be.
'US' in a CHAR(5)
column, it stores 'US ' (3 trailing spaces).
Faster for equality comparisons on fixed values.VARCHAR(100) only uses 6 bytes.| Stored Value | CHAR(10) uses | VARCHAR(10) uses | Difference |
|---|---|---|---|
| 'Hi' | 10 bytes (padded) | 3 bytes (2+1) | −7 bytes |
| 'Hello' | 10 bytes | 6 bytes (5+1) | −4 bytes |
| 'Hi Priya' | 10 bytes | 9 bytes (8+1) | −1 byte |
| 'SomeString' | 10 bytes | 11 bytes (10+1) | same length |
| '' (empty) | 10 bytes | 1 byte | −9 bytes |
CREATE TABLE articles (
article_id INT AUTO_INCREMENT PRIMARY KEY,
country_code CHAR(2) NOT NULL, -- 'IN', 'US' always 2 chars
title VARCHAR(200) NOT NULL, -- variable length title
slug VARCHAR(220) NOT NULL UNIQUE, -- URL-friendly identifier
excerpt VARCHAR(500), -- short preview text
body MEDIUMTEXT NOT NULL, -- full article content
meta_desc VARCHAR(160) -- SEO meta description
);
Date & Time Data Types
Date and time types are essential for any application that tracks when things happened — orders, logins, appointments, sensor readings. Using the right type prevents timezone bugs and future date-range errors.
2024-03-15. Perfect when the time of day is
irrelevant.DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP.| Feature | DATETIME | TIMESTAMP |
|---|---|---|
| Storage | 8 bytes | 4 bytes ✅ smaller |
| Range | 1000–9999 ✅ wider | 1970–2038 (Y2K38 problem!) |
| Timezone | Not timezone-aware | Stored as UTC, auto-converts ✅ |
| DEFAULT CURRENT_TIMESTAMP | MySQL 5.6+ only | ✅ All versions |
| ON UPDATE CURRENT_TIMESTAMP | MySQL 5.6+ only | ✅ Native support |
| Best for | Scheduled future events, historical data | Row audit columns (created_at, updated_at) |
CREATE TABLE events (
event_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(150) NOT NULL,
event_date DATE NOT NULL, -- 2025-06-15
start_time TIME NOT NULL, -- 09:30:00
scheduled_at DATETIME NOT NULL, -- no TZ conversion
created_at TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP
);
BOOLEAN, ENUM & SET
These types handle yes/no flags, fixed-option lists, and multi-select fields — all very common in real applications.
TINYINT(1). PostgreSQL has a native BOOLEAN type. You can insert TRUE, FALSE, 1, or 0.'Mon,Wed,Fri'. Queried
with FIND_IN_SET().CREATE TABLE users (
user_id INT AUTO_INCREMENT PRIMARY KEY,
-- Boolean: is this account active?
is_active BOOLEAN NOT NULL DEFAULT TRUE,
is_admin TINYINT(1) NOT NULL DEFAULT 0,
-- ENUM: exactly ONE value from the list
role ENUM('student','teacher','admin')
NOT NULL DEFAULT 'student',
status ENUM('active','suspended','deleted')
NOT NULL DEFAULT 'active',
-- SET: MULTIPLE values from the list
permissions SET('read','write','delete','export')
NOT NULL DEFAULT 'read'
);
ALTER TABLE which
can lock the table on large datasets. If your option list changes frequently, consider a
separate lookup table with a FOREIGN KEY instead.
Binary (BLOB) & JSON Types
These two types cover the extremes: raw binary data (files, images, hashes) and semi-structured document data (JSON). Both are stored inside the database itself, which is sometimes the right choice — and sometimes not.
JSON_EXTRACT() or
the -> operator. Much better than storing JSON as
TEXT.CREATE TABLE user_profiles (
profile_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL UNIQUE,
-- 16-byte MD5 hash stored efficiently
password_hash BINARY(32) NOT NULL, -- SHA-256 = 32 bytes
-- Profile image stored as file URL (not BLOB!)
avatar_url VARCHAR(500),
-- Flexible settings stored as JSON
preferences JSON, -- e.g. {"theme":"dark","lang":"en"}
metadata JSON -- API response data, dynamic fields
);
-- Querying JSON fields (MySQL)
SELECT user_id, preferences->'$.theme' AS theme
FROM user_profiles
WHERE preferences->>'$.lang' = 'en';
Understanding NULL in SQL
NULL is not a data type itself — it is a special marker meaning
"no value exists" or "unknown". It is critically different from
an empty string '' or the number
0. Understanding NULL is essential for
writing correct SQL.
| student_id | name | phone | age | grade |
|---|---|---|---|---|
| 1 | Priya Sharma | +91-9876543210 | 22 | A |
| 2 | Rahul Patel | NULL (no phone provided) | 25 | NULL (not graded yet) |
| 3 | Anika Desai | '' (empty string — wrong!) | 0 (wrong! 0 ≠ unknown) | F |
WHERE phone = NULL always returns zero rows. You must
use WHERE phone IS NULL or WHERE phone IS NOT NULL.-- Find students whose phone number is missing
SELECT name FROM students WHERE phone IS NULL;
-- Find students who DO have a phone number
SELECT name, phone FROM students WHERE phone IS NOT NULL;
-- Replace NULL with a fallback value using COALESCE
SELECT name, COALESCE(phone, 'No phone') AS contact
FROM students;
-- NULL arithmetic always returns NULL
SELECT 100 + NULL; -- → NULL
SELECT NULL = NULL; -- → NULL (not TRUE!)
Quick Decision Guide — Which Type Should I Use?
Stop guessing. Here's the fast-reference card for the most common column scenarios:
Data Type Differences Across SQL Databases
The SQL standard defines core data types, but each database system has its own extensions and naming quirks. Here are the key differences you'll encounter:
| Concept | MySQL / MariaDB | PostgreSQL | SQL Server | SQLite |
|---|---|---|---|---|
| Boolean | TINYINT(1) |
BOOLEAN |
BIT |
INTEGER (0/1) |
| Auto-increment PK | INT AUTO_INCREMENT |
SERIAL / GENERATED ALWAYS AS IDENTITY |
INT IDENTITY(1,1) |
INTEGER PRIMARY KEY |
| Variable string | VARCHAR(n) |
VARCHAR(n) / TEXT |
NVARCHAR(n) |
TEXT |
| Current timestamp default | DEFAULT CURRENT_TIMESTAMP |
DEFAULT NOW() |
DEFAULT GETDATE() |
DEFAULT (datetime('now')) |
| JSON | JSON (MySQL 5.7+) |
JSON / JSONB
|
Store as NVARCHAR |
Store as TEXT |
| Exact decimal | DECIMAL(p,s) |
NUMERIC(p,s) |
DECIMAL(p,s) |
REAL / NUMERIC |
Try It Yourself
Explore the products and
students tables below — both are populated with
sample data showcasing various data types: numeric, string, date, ENUM, boolean.
Run SELECT queries to see how different types behave.
products (id, name, price, stock, status, created_date) ·
students (id, name, city, age, grade, is_active)
· Try:
Which SQL data type should you use to store a price value like 4999.99 accurately without any rounding errors?
- Every column must have a data type — it determines what values are valid, how much space is used, and what operations are allowed.
- INT (4 bytes, ±2.1 billion) is the default integer choice for primary keys and counts. Use BIGINT only when INT's limit is genuinely exceeded.
- Always use DECIMAL(p, s) for money and exact values. Never use FLOAT or DOUBLE for financial data — they introduce rounding errors.
- VARCHAR(n) is the default string type — variable-length, efficient. Use CHAR(n) only for truly fixed-length values like country codes.
- For large text (articles, HTML), use TEXT or MEDIUMTEXT. TEXT cannot have a DEFAULT value and cannot be fully indexed.
- TIMESTAMP is timezone-aware and perfect for
created_at/updated_ataudit columns withDEFAULT CURRENT_TIMESTAMP. DATETIME is better for scheduled future events. - MySQL's BOOLEAN is stored as
TINYINT(1). ENUM holds one value from a fixed list; SET holds multiple values from a fixed list. - NULL means "no value" — not zero, not empty string. Use
IS NULL/IS NOT NULLto query nulls; useCOALESCE()to substitute a fallback value. - Store large files (images, videos) externally (S3/GCS) and save only the URL in the database. Only use BLOB for small binary payloads like hashes or tokens.
- The JSON type (MySQL 5.7+, PostgreSQL) stores validated JSON with path-based query support — always prefer it over storing JSON in a TEXT column.
Rate Us on Google
Join Our Learning Community
Connect with thousands of learners, get daily tips, ask questions and stay updated with new tutorials.