We recommend the following best practices for writing your SQL queries and stored procedures.
- Comments at the top of your query explain purpose of the view/procedure.
- Inline comments are used to document specific sections of your code, calculations and joins.
- Indent your code where doing so makes it easier to read/troubleshoot. One field per line and commas at the beginning of the line makes it easy to comment out specific lines during testing/debugging.
- Use ALL CAPS for reserved SQL keywords (SELECT, FROM, SUM, etc.) and, conversely, avoid using all caps for non-reserved words and variable names.
- Choose table aliases that give some hint to the source of the data. This makes it much easier to troubleshoot code. In the example below, try guessing which fields come from which tables without looking at the table aliases and notice how much more obvious (sch, dis, tch) it is than (a, b, c).
- Don’t select columns that you aren't going to use. Generally, you should avoid using SELECT * unless there is a good reason to do so.