Every column in a SQL table must have a data type that defines what kind of data it can store. Choosing the right type is crucial for integrity, performance, and storage efficiency.
String Types — Text data from short codes to long articles
Numeric Types — Integers, decimals, and floating-point numbers
Date/Time Types — Dates, times, and timestamps
The right data type saves space, enforces rules, and speeds up queries.
Data Type Categories
String Types
CREATE TABLE products (
sku CHAR(10), -- Fixed length (always 10 chars)
name VARCHAR(255), -- Variable length (up to 255)
description TEXT, -- Very large text
notes VARCHAR(500) -- Variable length with limit
);
String Type Comparison
Type
Max Size
Storage
Best For
Example
CHAR(n)
n characters
n bytes
Fixed codes, IDs
US state codes: 'CA', 'NY'
VARCHAR(n)
n characters
Actual length + 1-2 bytes
Names, emails
'Alice Johnson'
TEXT
~65KB
Actual length + 1-2 bytes
Articles, descriptions
Blog content
CLOB
Very large
Variable
Document storage
Full articles
CHAR vs VARCHAR
Unicode String Types
-- For international text (Chinese, Arabic, emojis, etc.)
CREATE TABLE international (
name NVARCHAR(100), -- Unicode variable length
description NTEXT, -- Unicode large text
-- MySQL equivalent:
name_utf8 VARCHAR(100) CHARACTER SET utf8mb4
);
Numeric Types
CREATE TABLE inventory (
price DECIMAL(10,2), -- Exact: 10 digits, 2 after decimal
quantity INTEGER, -- Whole numbers
rating SMALLINT, -- Small integer
product_code BIGINT, -- Large integer
weight FLOAT, -- Approximate decimal
dimensions REAL -- Single precision float
);
Integer Types
Type
Storage
Min Value
Max Value
Use Case
SMALLINT
2 bytes
-32,768
32,767
Age, quantity, status codes
INTEGER
4 bytes
-2,147,483,648
2,147,483,647
IDs, counts, general numbers
BIGINT
8 bytes
-9.2 quintillion
9.2 quintillion
Large counters, timestamps
Decimal vs Float
Type
Precision
Storage
Use Case
DECIMAL(p,s)
Exact
Variable
Money, financial data
NUMERIC(p,s)
Exact
Variable
Same as DECIMAL
FLOAT
~7 digits
4 bytes
Scientific calculations
REAL
~7 digits
4 bytes
Same as FLOAT
DOUBLE
~15 digits
8 bytes
High precision scientific
-- DECIMAL for money (exact precision)
CREATE TABLE transactions (
id INTEGER PRIMARY KEY,
amount DECIMAL(10,2), -- 10 total digits, 2 after decimal
tax_rate DECIMAL(5,4), -- 5 total digits, 4 after decimal
total DECIMAL(12,2)
);
-- Example values
INSERT INTO transactions VALUES (1, 99.99, 0.0825, 108.24);
-- Current date and time functions
SELECT CURRENT_DATE; -- Today's date
SELECT CURRENT_TIME; -- Current time
SELECT CURRENT_TIMESTAMP; -- Current date and time
-- Date arithmetic
SELECT CURRENT_DATE + INTERVAL '7' DAY; -- One week from now
SELECT CURRENT_DATE + INTERVAL '1' MONTH; -- One month from now
SELECT CURRENT_DATE - INTERVAL '3' YEAR; -- Three years ago
-- Extract components
SELECT EXTRACT(YEAR FROM CURRENT_DATE); -- 2024
SELECT EXTRACT(MONTH FROM CURRENT_DATE); -- 1
SELECT EXTRACT(DAY FROM CURRENT_DATE); -- 15
Boolean Type
CREATE TABLE users (
id INTEGER PRIMARY KEY,
username TEXT NOT NULL,
is_active BOOLEAN DEFAULT TRUE,
is_verified BOOLEAN DEFAULT FALSE,
email_verified_at TIMESTAMP
);
-- Query with booleans
SELECT * FROM users WHERE is_active = TRUE;
SELECT * FROM users WHERE is_verified;
SELECT * FROM users WHERE NOT is_verified;
Binary Types
CREATE TABLE files (
id INTEGER PRIMARY KEY,
filename TEXT NOT NULL,
file_data BLOB, -- Binary Large Object
file_hash BINARY(32), -- Fixed length binary (SHA-256)
thumbnail VARBINARY(10000) -- Variable length binary
);
Data Type Selection Guide
Common Mistakes
-- BAD: Using TEXT for everything
CREATE TABLE bad_table (
age TEXT, -- Should be SMALLINT
price TEXT, -- Should be DECIMAL
is_active TEXT -- Should be BOOLEAN
);
-- GOOD: Appropriate data types
CREATE TABLE good_table (
age SMALLINT CHECK (age >= 0 AND age <= 150),
price DECIMAL(10,2) CHECK (price >= 0),
is_active BOOLEAN DEFAULT TRUE
);