Tutorials SQL SQL Data Types
Ch 8 / 69
Advertisement
📢Ad space · 728 × 90 · Replace slot ID above
Chapter 8 Beginner 8 min read DDL

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.

What you'll learn
All numeric types: INT, BIGINT, DECIMAL, FLOAT
String types: CHAR, VARCHAR, TEXT, MEDIUMTEXT
Date & time: DATE, TIME, DATETIME, TIMESTAMP
BOOLEAN, TINYINT(1) and how they relate
ENUM, SET — categorical/option types
BLOB, BINARY — binary/file storage
JSON data type in modern MySQL/PostgreSQL
NULL — what it means and when to allow it
How to choose the right type for each scenario

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:

🔢
Numeric
INT · BIGINT · DECIMAL
FLOAT · DOUBLE · TINYINT
Numbers, IDs, quantities, money, measurements
🔤
String
VARCHAR · CHAR · TEXT
MEDIUMTEXT · LONGTEXT
Names, emails, addresses, descriptions, code
📅
Date & Time
DATE · TIME · DATETIME
TIMESTAMP · YEAR
Birthdates, appointments, order times, logs
☑️
Boolean / Enum
BOOLEAN · TINYINT(1)
ENUM · SET
Flags, status fields, fixed option lists
💾
Binary
BLOB · MEDIUMBLOB
BINARY · VARBINARY
Images, files, hashed passwords (rare)
📋
JSON
JSON
Semi-structured documents, flexible config data
Why Data Types Matter
SQL engines use data types for three things: validation (rejecting invalid values), storage optimisation (INT uses 4 bytes, BIGINT uses 8 — choose carefully), and operation rules (you can do arithmetic on INT but not VARCHAR). A wrong type is impossible to use correctly at query time.

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.

🔢 Integer Types — Whole Numbers
TINYINT
1 byte
Signed: –128 to 127  ·  UNSIGNED: 0–255
Smallest integer type. Perfect for very small numbers or two-state flags. TINYINT(1) is MySQL's internal representation of BOOLEAN.
✅ Boolean flags, age (small range), star ratings
SMALLINT
2 bytes
Signed: –32,768 to 32,767  ·  UNSIGNED: 0–65,535
Medium-small integer. Useful for numeric codes, years, or column counts that stay in the tens of thousands.
✅ Year codes, postal codes, short counters
INT / INTEGER
4 bytes
Signed: –2,147,483,648 to 2,147,483,647
UNSIGNED: 0 to 4,294,967,295
The standard integer. Handles virtually all IDs and counters for typical applications. Use with AUTO_INCREMENT for primary keys.
✅ Primary keys, order IDs, quantities — default choice
BIGINT
8 bytes
Signed: –9.2 × 10¹⁸ to 9.2 × 10¹⁸
Enormous integer range. Required only when INT's 2-billion limit isn't enough — high-volume event logs, IoT streams, distributed system IDs (Snowflake IDs).
⚠️ Only use when INT's limit is genuinely exceeded
MEDIUMINT
3 bytes
Signed: –8,388,608 to 8,388,607
Between SMALLINT and INT. Rarely needed but saves 1 byte vs INT in very large tables. MySQL-specific.
✅ Large catalogue IDs, ZIP codes
📐 Exact & Approximate Decimal Types
DECIMAL(p, s)
Variable
p = total digits (precision) · s = digits after decimal (scale)
e.g. DECIMAL(10,2) → 99999999.99
Exact decimal storage. The value stored is exactly what you put in — no rounding errors. Alias: NUMERIC(p,s).
✅ Money, prices, tax, financial data — always use this
FLOAT
4 bytes
~7 significant decimal digits of precision (approx)
Approximate floating-point. Uses IEEE 754 binary representation — can introduce tiny rounding errors like 0.1 + 0.2 ≠ 0.3.
⚠️ Never use for money — use for scientific measurements
DOUBLE
8 bytes
~15–16 significant decimal digits of precision (approx)
Double-precision floating-point. More accurate than FLOAT but still approximate. Alias: DOUBLE PRECISION, REAL.
⚠️ Scientific data only — still not safe for currency
SQL — Numeric types in a real table
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.

CHAR(n)
n bytes fixed
Max n: 255 chars. Always stores exactly n bytes (pads with spaces).
Fixed-length string. If you store 'US' in a CHAR(5) column, it stores 'US ' (3 trailing spaces). Faster for equality comparisons on fixed values.
✅ Country codes (2), phone area codes, status flags
VARCHAR(n)
var + 1–2 bytes
Max n: 65,535 chars (limited by row size). Stores only what is actually in the string.
Variable-length string. The most common string type. Only uses the space the actual value needs — a 5-character name in VARCHAR(100) only uses 6 bytes.
✅ Names, emails, URLs, addresses — default string choice
TEXT
var + 2 bytes
Max: 65,535 characters (~64 KB)
For longer text content that exceeds VARCHAR limits. Cannot have a DEFAULT value. Cannot be fully indexed — only a prefix can be indexed.
✅ Blog posts, comments, product descriptions
MEDIUMTEXT
var + 3 bytes
Max: 16,777,215 characters (~16 MB)
For large text content — full articles, HTML pages, lengthy documents stored directly in the database.
✅ Full articles, raw HTML, large documents
LONGTEXT
var + 4 bytes
Max: 4,294,967,295 characters (~4 GB)
For enormous text data. Rarely needed for typical applications.
⚠️ Only for very large text blobs — use file storage instead
📊 CHAR(10) vs VARCHAR(10) — Storage Comparison
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
✅ Rule of thumb: Use CHAR only when every value is the same fixed length (e.g. country code, MD5 hash). Use VARCHAR for everything else.
SQL — String types in a real table
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
);
Advertisement
📢Ad space · 728 × 90 · Replace slot ID above

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.

DATE
3 bytes
Format: YYYY-MM-DD  ·  Range: 1000-01-01 to 9999-12-31
Stores only the date — no time component. Displays as 2024-03-15. Perfect when the time of day is irrelevant.
✅ Birthdates, event dates, deadlines, hire dates
TIME
3 bytes
Format: HH:MM:SS  ·  Range: −838:59:59 to 838:59:59
Stores a duration or time-of-day. Can hold values over 24 hours (useful for elapsed time). No date component.
✅ Opening hours, course durations, elapsed time
DATETIME
8 bytes
Format: YYYY-MM-DD HH:MM:SS  ·  Range: 1000-01-01 to 9999-12-31
No timezone conversion. Stores the exact date and time you insert — what you put in is what comes out, regardless of server timezone.
✅ Appointments, scheduled events, historical records
TIMESTAMP
4 bytes
Format: YYYY-MM-DD HH:MM:SS  ·  Range: 1970-01-01 to 2038-01-19
Timezone-aware. Stored as UTC, converted to server/session timezone on read. Supports DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP.
✅ created_at, updated_at audit columns — default time choice
YEAR
1 byte
Format: YYYY  ·  Range: 1901–2155
Stores only a 4-digit year. The most compact date type. Limited range but very space-efficient.
✅ Birth year, graduation year, manufacturing year
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)
SQL — Date/time types in practice
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.

☑️ Boolean Type
BOOLEAN / BOOL
1 byte
Values: TRUE (1) or FALSE (0)
MySQL internally stores BOOLEAN as TINYINT(1). PostgreSQL has a native BOOLEAN type. You can insert TRUE, FALSE, 1, or 0.
✅ is_active, is_verified, is_deleted flags
📋 ENUM & SET Types
ENUM('a','b','c')
1–2 bytes
1–2 bytes. Max 65,535 distinct values.
Stores one value from a predefined list. The database only accepts listed values and rejects anything else. Stored as an integer index internally — very space-efficient.
✅ status ('active','inactive','pending'), gender, priority
SET('a','b','c')
1–8 bytes
1–8 bytes (bit-mask). Max 64 members.
Like ENUM but can hold multiple values at once. Stored as a bitmask — e.g. 'Mon,Wed,Fri'. Queried with FIND_IN_SET().
✅ Days of week, multi-select permissions, tags (small sets)
SQL — BOOLEAN, ENUM and SET in use
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'
);
ENUM Caveat
Changing ENUM values later requires an 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.
Advertisement
📢Ad space · 728 × 90 · Replace slot ID above

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.

💾 Binary / BLOB Types
BINARY(n)
n bytes
Fixed-length binary. Max 255 bytes.
Like CHAR but for binary data. Every value is padded to exactly n bytes. Good for fixed-size hashes like MD5 (16 bytes) or SHA-256 (32 bytes).
✅ Storing hashed passwords, checksums, UUIDs (binary form)
VARBINARY(n)
var + 1–2 bytes
Variable-length binary. Max 65,535 bytes.
Variable-length binary data. Like VARCHAR but no character encoding — stores raw bytes. Useful for tokens and keys.
✅ Auth tokens, session keys
BLOB
var + 2 bytes
Max: 65,535 bytes (~64 KB)
Binary Large Object. Stores raw binary files — images, PDFs, audio clips. Performance degrades as BLOBs grow large. Consider object storage (S3) for files.
⚠️ Small files only — prefer object storage for large files
MEDIUMBLOB / LONGBLOB
up to 4 GB
MEDIUMBLOB: ~16 MB  ·  LONGBLOB: ~4 GB
Large and extra-large binary storage. LONGBLOB can technically hold entire video files — but don't do this. Always store large files externally and keep only the URL in the database.
⚠️ Almost never justified — use S3, GCS, or CDN instead
📋 JSON Type (MySQL 5.7+ / PostgreSQL 9.2+)
JSON
var (binary-optimised)
No fixed max (subject to max_allowed_packet in MySQL)
Stores valid JSON documents with automatic validation. Supports efficient path-based queries with JSON_EXTRACT() or the -> operator. Much better than storing JSON as TEXT.
✅ Config objects, dynamic attributes, API response caching
SQL — BINARY, BLOB and JSON in practice
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.

⚠️ NULL vs Empty String vs Zero — They are NOT the same
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
NULL
The value is absent / unknown. The phone number was never entered. The grade hasn't been assigned yet. NULL propagates — any arithmetic involving NULL produces NULL.
'' (empty string)
A string with zero characters. The column has a value — it's just empty. An empty phone number is not the same as a missing phone number.
0 (zero)
The integer value zero — a real, known quantity. An age of 0 means newborn, not unknown. Don't use 0 to represent "missing" — use NULL.
IS NULL / IS NOT NULL
Because NULL is not equal to anything (even itself), WHERE phone = NULL always returns zero rows. You must use WHERE phone IS NULL or WHERE phone IS NOT NULL.
SQL — Querying and handling NULL values
-- 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:

🔑
Primary key / auto-increment ID
INT AUTO_INCREMENT
💰
Price, salary, financial amount
DECIMAL(10, 2)
👤
Name, email, username, URL
VARCHAR(n)
🌏
Country code, currency code (fixed 2–3 chars)
CHAR(2) or CHAR(3)
📝
Blog post body, article content
MEDIUMTEXT
🎂
Birthday, hire date, event date
DATE
🕐
Row created/updated timestamp
TIMESTAMP DEFAULT CURRENT_TIMESTAMP
📅
Scheduled meeting, future appointment
DATETIME
✅
Yes/No flag (is_active, is_verified)
BOOLEAN / TINYINT(1)
🏷️
Fixed status (active/inactive/pending)
ENUM('active','inactive','pending')
📊
Very large counter / high-volume ID
BIGINT UNSIGNED
⚙️
User settings, config object
JSON
📊
Free Resource
Download our SQL Cheat Sheet
All data types, constraints, and commands — one printable page.
Get Sheet

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.

SQL Query Runner In-Browser
Tables: products (id, name, price, stock, status, created_date) · students (id, name, city, age, grade, is_active)  ·  Try:
Results will appear here after you run a query
Quick Check — Chapter 8

Which SQL data type should you use to store a price value like 4999.99 accurately without any rounding errors?

Chapter Summary
  • 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_at audit columns with DEFAULT 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 NULL to query nulls; use COALESCE() 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.
Advertisement
📢Ad space · 728 × 90 · Replace slot ID above

Rate Us on Google

4.8 / 5
Enjoying this tutorial?
Your 5-star review helps other learners discover our free tutorials and encourages us to keep creating quality content.
80+ reviews
Write a Google Review
Join WhatsApp Channel