Applies when writing or reviewing SQL in any dialect. Covers query performance, schema design, naming conventions, transactions, migrations, and anti-patterns.
When generating SQL queries, schemas, or migrations, apply every applicable rule below. These are dialect-neutral; load a sql-{dialect} skill alongside this one for dialect-specific patterns.
SELECT * in production code. List only the columns needed. SELECT * breaks covering indexes and causes silent breakage when schema changes.WHERE, JOIN, and ORDER BY column in frequent queries. Composite indexes: equality filters first, then range filters, then sort columns (leftmost-prefix rule).EXPLAIN (or EXPLAIN ANALYZE) before deploying any query on non-trivial tables. Watch for sequential scans, nested loops on unindexed columns, sort spills.WHERE YEAR(created_at) = 2025 kills the index. Rewrite as WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'.EXISTS instead of IN for correlated existence checks on large result sets. EXISTS short-circuits on first match.OFFSET for deep pagination. Use keyset pagination: WHERE id > :last_seen ORDER BY id LIMIT :n.INSERT ... VALUES (...),(...),... or COPY/bulk-load. Never one row per round-trip.OR across different columns — it prevents index usage. Rewrite as UNION ALL of indexed queries.SMALLINT over INT when it fits, DATE not TIMESTAMP for calendar dates, NUMERIC(p,s) not FLOAT for money.NOT NULL on every column unless NULL has defined semantics.CHECK constraints for domain validation (CHECK (price >= 0), CHECK (status IN ('active','inactive'))).UNIQUE constraints on natural-key columns (email, username) even with surrogate PKs.TIMESTAMP WITH TIME ZONE for point-in-time values. Store UTC, convert in the presentation layer.created_at and updated_at to every mutable table with defaults and auto-update triggers.snake_case for all identifiers. Never camelCase or PascalCase in SQL.user, order, product).is_* or has_* (is_active, has_verified_email).<table>_id (user_id, order_id).ix_<table>_<cols>, unique: ux_<table>_<col>, foreign keys: fk_<table>_<ref>.user, order, group). Rename to app_user, customer_order, etc.READ COMMITTED as the default isolation. Document explicitly when using higher levels.IF NOT EXISTS, IF EXISTS guards on all DDL.CONCURRENTLY in PG, ALGORITHM=INPLACE in MySQL).JOIN or IN (...).WHERE varchar_col = 12345 casts every row, kills indexes.SELECT DISTINCT to hide bad joins. Fix the join conditions.JOIN or window functions suffice.HAVING for conditions that belong in WHERE. WHERE filters before aggregation.is_deleted without a partial index. Add a partial index on active rows.FLOAT/DOUBLE for money. Use NUMERIC/DECIMAL.IS NULL / IS NOT NULL, never = NULL. = NULL evaluates to UNKNOWN.COALESCE(expr, default) for fallback values.NOT IN with NULLs returns no rows. Use NOT EXISTS instead.COUNT(*) counts rows; COUNT(col) skips NULLs. Choose deliberately.GROUP BY when you need one row per group (aggregation that reduces rows).ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) for top-N-per-group. Never use correlated subqueries for this.OVER w ... WINDOW w AS (...) for reuse.CYCLE clause).