SQL Fundamentals
SQL Data Types
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
DfData Type
A definition of the kind of data a column can hold, including how it is stored, validated, and used in calculations. Data types enforce constraints at the database level, preventing invalid data from being inserted.
String Types
DfString Data Type
A data type for storing text or character data. Strings can be fixed-length (CHAR) or variable-length (VARCHAR), with varying maximum sizes depending on the database system.
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
Use CHAR when data always has the same length (like state codes or country codes). Use VARCHAR when text length varies β it saves storage space by not padding with spaces.
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
DfNumeric Data Type
A data type for storing numbers, which can be integers (whole numbers) or decimals (numbers with fractional parts). The choice depends on the range of values and whether exact precision is required.
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
DfDECIMAL
An exact numeric type that stores numbers with a fixed number of decimal places. Perfect for financial data where precision matters β 0.1 + 0.2 always equals 0.3.
DfFLOAT
An approximate numeric type that uses binary floating-point representation. Faster for calculations but can introduce rounding errors β 0.1 + 0.2 may not equal exactly 0.3.
| 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);
Never use FLOAT for financial calculations. Floating-point arithmetic can introduce rounding errors that compound over time. Always use DECIMAL for money.
Date and Time Types
DfDate/Time Types
Data types for storing temporal information β dates (year, month, day), times (hour, minute, second), or both. Most databases also support time zones and interval arithmetic.
CREATE TABLE events (
event_date DATE, -- 2024-01-15
event_time TIME, -- 14:30:00
event_timestamp TIMESTAMP, -- 2024-01-15 14:30:00
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
| Type | Format | Example | Storage |
|---|---|---|---|
DATE | YYYY-MM-DD | 2024-01-15 | 3 bytes |
TIME | HH:MM:SS | 14:30:00 | 3 bytes |
TIMESTAMP | YYYY-MM-DD HH:MM:SS | 2024-01-15 14:30:00 | 4-8 bytes |
DATETIME | YYYY-MM-DD HH:MM:SS | 2024-01-15 14:30:00 | 8 bytes |
INTERVAL | Varies | INTERVAL '1' DAY | 16 bytes |
Date Functions
-- 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
DfBOOLEAN
A data type that represents true/false values. Some databases store BOOLEAN as BIT (0/1) internally, but the SQL interface accepts TRUE/FALSE literals.
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
Using TEXT for everything (age, price, is_active) is a common mistake. Always choose the most appropriate data type β it saves storage, improves performance, and enforces data integrity.
-- 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
);
Summary
Key Takeaways
- Each column needs a data type that defines what it can store
- Use VARCHAR for variable text, CHAR for fixed-length codes
- Use DECIMAL for exact values like money, FLOAT for scientific data
- Choose the smallest type that fits your data range
- Use BOOLEAN for true/false values instead of TEXT or INTEGER