What is the difference between WHERE and HAVING?
WHERE filters individual rows before any grouping happens, so it cannot reference aggregate functions. HAVING filters groups after GROUP BY has run, so it can reference aggregates such as COUNT(*) or SUM(amount). As a rule, put a condition in WHERE whenever it applies to raw rows, because filtering early reduces the number of rows the database must group; reserve HAVING for conditions that depend on the aggregated result.
When should I add an index, and when does it hurt?
Add an index when a column is frequently used in WHERE, JOIN, or ORDER BY predicates and is selective enough that the index narrows the search meaningfully. Indexes hurt write performance because every INSERT, UPDATE, and DELETE must also maintain them, and they consume storage, so unused or redundant indexes are pure overhead. Confirm an index is actually used by reading the EXPLAIN plan; an index on a low-cardinality column or one never referenced by the planner is wasted cost.
How do I avoid the N+1 query problem with an ORM?
The N+1 problem happens when loading N parent rows triggers one extra query per parent to fetch a related collection, totalling N+1 round trips. Fix it by fetching the association in the same query: use a JOIN FETCH (JPA/Hibernate), include/select eager loading (Prisma), or selectinload/joinedload (SQLAlchemy). Always verify the generated SQL with query logging, because the fix is to load exactly the data the use case needs in as few round trips as possible.
What is the difference between a clustered and a non-clustered index?
A clustered index defines the physical order of the table's rows, so the row data lives in the index leaves and a table can have only one. A non-clustered (secondary) index is a separate structure holding the indexed columns plus a pointer back to the row, so a table can have many. In MySQL/InnoDB and SQL Server the primary key is clustered by default, which makes lookups by primary key very fast and makes secondary index lookups pay an extra step to fetch the full row.
Should I use a natural key or a surrogate key as my primary key?
Prefer a surrogate key (auto-increment integer or UUID) in most operational schemas because it is stable, compact, and never needs to change when business attributes do, which keeps foreign keys simple and immutable. A natural key can be appropriate when a genuinely stable, unique business identifier exists and you want the database to enforce it directly. You can have both: a surrogate primary key plus a UNIQUE constraint on the natural business key to enforce its uniqueness.
How do isolation levels prevent concurrency anomalies?
Isolation levels trade concurrency for consistency by controlling which effects of other transactions a transaction can see. Read Committed prevents dirty reads, Repeatable Read additionally prevents non-repeatable reads, and Serializable additionally prevents phantom reads, behaving as if transactions ran one at a time. Most databases default to Read Committed; raise the level only for the specific transactions that need it, because stronger isolation increases blocking or serialization-failure retries.
What causes a deadlock and how do I prevent it?
A deadlock arises when two transactions each hold a lock the other needs, so neither can advance; the database detects the cycle and aborts one transaction as the victim. Prevent deadlocks by acquiring locks in a consistent order across all code paths, keeping transactions short, and touching rows in a predictable sequence. Because deadlocks cannot be fully eliminated under concurrency, applications should also wrap transactions in retry logic that re-runs the aborted transaction.
When should I denormalize a schema?
Normalize first: a normalized schema stores each fact once, which makes writes correct and simple. Denormalize only when profiling shows a specific read path is too slow because of repeated joins or aggregations, and the read-to-write ratio is high. The cost of denormalization is keeping redundant data consistent, so prefer mechanisms the database can maintain for you, such as materialized views or triggers, over scattering update logic across the application.
How do I paginate large result sets efficiently?
OFFSET/LIMIT pagination is simple but degrades on deep pages because the database must scan and discard all skipped rows. Keyset (cursor) pagination is far more efficient at scale: order by a unique, indexed column and fetch the next page with WHERE (sort_col, id) > (:last_sort, :last_id) ORDER BY sort_col, id LIMIT n. Keyset pagination keeps every page fast because it seeks directly into the index instead of counting from the start.
What is the difference between DELETE, TRUNCATE, and DROP?
DELETE removes rows matching a WHERE clause, is fully transactional and logged, and fires row triggers, so it can be rolled back. TRUNCATE removes all rows quickly by deallocating data pages, usually resets identity/sequence counters, and in many engines cannot be filtered or is non-transactional. DROP removes the table object itself, including its data, indexes, and constraints. Choose DELETE for selective, recoverable removal; TRUNCATE for fast full-table clears; DROP to discard the structure.
How do prepared statements prevent SQL injection?
A prepared statement sends the SQL text with parameter placeholders to the database first, where it is parsed and planned, and then sends the parameter values separately. Because the values arrive after the statement structure is fixed, they can never change the meaning of the query, so attacker input is treated strictly as data. This separation is the definitive defence against SQL injection, far more reliable than trying to escape or sanitise concatenated strings.
What is a window function and how does it differ from GROUP BY?
A window function computes a value over a set of related rows (the window defined by OVER (...)) while still returning every input row, unlike GROUP BY, which collapses each group into a single row. This lets you produce running totals, rankings (ROW_NUMBER, RANK), and access neighbouring rows (LAG, LEAD) alongside the detail data. Use a window function whenever you need an aggregate-style computation but must keep the individual rows visible.
How should I store dates and times to avoid timezone bugs?
Store instants in a timezone-aware type that normalises to UTC, such as PostgreSQL timestamptz, and convert to the user's local zone only at the presentation layer. Storing naive local times invites ambiguity around daylight-saving transitions and makes cross-region comparison error-prone. Keep the database as the single source of truth in UTC, and never store a local time without also recording its zone.
What is the difference between a view and a materialized view?
A plain view is a stored query that is recomputed every time it is referenced, so it always reflects the current base tables and stores no data. A materialized view stores the precomputed result physically, making reads fast but requiring an explicit refresh to pick up changes, so its data can be stale. Use a view to simplify and secure access to live data; use a materialized view to cache expensive aggregations that tolerate some staleness.
How do I diagnose a slow query?
Start with EXPLAIN (and EXPLAIN ANALYZE to get actual timings and row counts), and look for sequential scans on large tables, large row-estimate errors, expensive sorts, and nested-loop joins over many rows. Compare estimated versus actual rows: a big discrepancy usually means stale statistics, so run ANALYZE. Then address the root cause, typically by adding or fixing an index, rewriting the query to be sargable, or updating statistics, and re-check the plan.
What does it mean for a query to be sargable?
A query is sargable (Search ARGument ABLE) when its predicates can use an index, which generally means the indexed column appears alone on one side of the comparison. Wrapping the column in a function, as in WHERE YEAR(created_at) = 2026, or applying arithmetic to it, usually defeats the index and forces a scan. Rewrite such predicates to leave the column bare, for example WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01', so the optimizer can seek the index.
When should I choose a relational database over a NoSQL store?
Choose a relational database when your data is highly interrelated, you need multi-row ACID transactions, ad-hoc querying with joins, and strong integrity enforced by constraints. Choose a NoSQL store when you need a specific access pattern at extreme scale, a flexible or rapidly evolving schema, or a data shape (document, key-value, graph, wide-column) that maps poorly to tables. Many systems use both: a relational database as the system of record and a specialised store for a particular workload.