Interview Question of the Week #028 – Few SQL Best Practices

Last week I moderated a SQL Server interview. The question sounded easy: “What SQL best practices do you follow?” The candidate went quiet. It reminded me of my older list of SQL commandments. An interview is a useful place to ask which of those practices a candidate can explain from experience.

Question: Which SQL best practices can you defend with an example?

Answer: Start with the data and the workload. Here are the practices I would discuss in an interview, with the reason behind each one. They keep the spirit of my original list while correcting a few rules that were too absolute.

1. Know what a row represents. Understand keys, relationships, data volume, and distribution before tuning a query. A join that doubles the result is wrong even if it runs quickly.
2. Test with realistic data. A tiny test table cannot reproduce the estimates, memory needs, or skew of a production workload. Compare actual plans and logical reads on representative data.
3. Use matching data types. A parameter with the wrong type or length can change the answer or add a conversion. Match the column and check whether conversion lands on the column side of a predicate.
4. Index for a workload, not for one screenshot. An index can help a filter, join, or sort, but it also costs storage and write work. Evaluate overlapping indexes and the effect on other queries.
5. Give the optimizer a clear search condition. A date range often communicates intent better than converting every stored date to text. Yet >, IS NULL, and NOT EXISTS are not automatically incapable of using an index. Inspect the actual plan.
6. Filter at the right stage. Use WHERE for conditions on input rows; use HAVING for conditions on groups after aggregation. One is not a universal replacement for the other.
7. Measure instead of memorizing a scan cutoff. My old list said a scan wins above 15 percent of rows. There is no universal percentage: row width, selectivity, ordering, covering, lookup cost, and storage all matter.
8. Parameterize values and protect identifiers. Parameters help separate data from executable SQL. A stored procedure is a useful interface, but it is not automatically precompiled at creation and does not sanitize concatenated dynamic SQL.
9. Consider the whole system. A hint or extra index that speeds one query can slow another or increase writes. Check concurrency, blocking, memory, and network traffic as well as one query’s elapsed time.
10. Keep the solution understandable. Set-based SQL often beats a row-by-row cursor, but a cursor can still be the right tool for a genuinely ordered per-row operation. Choose from evidence, then make the reasoning easy to maintain.

In an interview, I would rather hear two well-explained examples from your own work than twenty commandments repeated without context. That is the point the candidate’s silence reminded me of. My original SQL commandments article remains available for readers who want the longer historical list.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.