Three guiding rules for writing SQL
May 16, 2024•222 words
These are my three guiding rules for writing good SQL
- Readable
- Maintainable
- Performant
If nothing else, first make it readable. Well-structured SQL is faster to review and easier to debug. Consistent style, use indentation, give identifiers meaningful names, and add comments.
Next focus on the maintainability of your query. Good news is that if your query is very readable, then it is already easier to maintain. Find ways to avoid repetitive logic or repeated placement of filters or parameters. If there are a lot of steps, consider breaking it up into smaller CTEs or subqueries. Most importantly don't sacrifice readability unless the benefit to maintainability is sufficiently high.
Performance is important but the last priority among these three. It's difficult to influence SQL performance since it's a declarative language. A good query can still run slow if indexes are missing or statistics are out of date. You should still pay attention to things like filters, joins, or expensive operations, but don't get stuck trying to squeeze out every last bit of speed. Shaving 5 seconds off a report that runs in 20 seconds is not worth the effort.
To sum up, favor readable and maintainable SQL over best performance. Only sacrifice readability and maintainability if the response time is unacceptable. Fortunately, well-structured SQL usually ends up performing best anyway.