SQL Best Practices

A reference guide for writing clean, efficient, and maintainable SQL.

Naming Conventions

Use snake_case for table and column names (customer_orders, not CustomerOrders)
Table names should be singular (customer, not customers)
Primary keys should be named id or table_name_id
Foreign keys should mirror the referenced column (customer_id)
Avoid reserved words as column names (date, name, type)
Be descriptive — created_at is better than dt

Query Writing

SELECT

Never use SELECT * in production queries — always name your columns explicitly
Alias columns when the name is ambiguous or computed: SUM(amount) AS total_amount
Use table aliases for readability, especially in joins: c.customer_id

WHERE

Always filter on indexed columns where possible

Avoid wrapping columns in functions in WHERE clauses — it prevents index use:

-- Bad
WHERE YEAR(created_at) = 2024

-- Good
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'

Use EXISTS instead of IN for subqueries against large tables

JOINs

Always use explicit JOIN syntax (INNER JOIN, LEFT JOIN) — never implicit comma joins
Join on indexed columns
Be explicit about join type — don't use JOIN when you mean LEFT JOIN
Filter in the ON clause for outer joins, not the WHERE clause

ORDER BY

Only use ORDER BY when order actually matters — it adds overhead
Never rely on implicit ordering

Indexing

Index foreign key columns
Index columns frequently used in WHERE, JOIN, and ORDER BY clauses
Avoid over-indexing — indexes slow down writes
Use composite indexes for multi-column filter patterns (column order matters)
Regularly review unused indexes

Data Types

Use the most specific type that fits the data (TINYINT vs INT vs BIGINT)
Use VARCHAR(n) with a realistic n — don't default everything to VARCHAR(255)
Store dates as DATE or DATETIME, not as strings
Use DECIMAL for currency — never FLOAT or DOUBLE
Use BOOLEAN / BIT for true/false flags

Transactions

BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

NULL Handling

Understand that NULL is not equal to anything, including itself
Use IS NULL / IS NOT NULL, never = NULL
Use COALESCE() to provide fallback values
Design columns to be NOT NULL where possible and use defaults

Performance

Use EXPLAIN / EXPLAIN ANALYZE to understand query plans
Avoid SELECT DISTINCT as a crutch — fix the root cause of duplicates
Use pagination (LIMIT / OFFSET or keyset pagination) for large result sets
Avoid correlated subqueries — rewrite as JOINs where possible
Use CTEs (WITH) for readability, but be aware some engines materialize them

Security

Always use parameterized queries / prepared statements — never string-concatenate user input
Apply principle of least privilege — app accounts should not have DBA rights
Avoid exposing internal IDs in APIs where possible
Audit and log access to sensitive tables
Never store passwords in plain text — store hashes only

Maintenance

Use migrations for all schema changes — never modify production schema manually
Document tables and columns with comments
Keep a changelog of significant schema changes
Regularly run ANALYZE / UPDATE STATISTICS to keep query planner accurate
Archive or partition large tables rather than letting them grow unbounded

Style

Capitalize SQL keywords: SELECT, FROM, WHERE, JOIN
One clause per line for complex queries
Indent subqueries and CTEs consistently
Comment non-obvious logic

-- Calculate 30-day rolling revenue per customer
SELECT
  c.id,
  c.name,
  SUM(o.amount) AS rolling_revenue
FROM customer c
INNER JOIN orders o
  ON o.customer_id = c.id
WHERE o.created_at >= DATEADD(day, -30, GETDATE())
GROUP BY c.id, c.name
ORDER BY rolling_revenue DESC;