SQL Best Practices

In this post I’m going to share some best practices for formatting SQL that I’ve learned and used over the last 20 years. Many of these tips are things that I’ve collected along the way out of frustration with inheriting code, needing to perform updates, chasing down bugs, and performing code reviews for others. Also, when it comes to training new employees, I have found that it is much more difficult for people to understand the domain and code when dealing with poorly written SQL.

In each section of this article, I’ll try to focus in on one area of practice. I’ll provide you with an example of a bad practice and example(s) of best practices along with my reasons behind writing code a specific way. As you’re reading you might think, “Well that isn’t as performant of code” and sometimes this might be the case because I’m trying to hone in on a specific point and avoid excess potential for confusion. Also, sometimes I prefer to have slightly less performant code (depending on how often it is used, the purpose of the code, and the performance hit) if it makes the code easier to read and maintain.

I’m guessing that some people reading this might say, “Well my code executes just fine so I don’t care. I understand what it does.” To that (because I’ve heard these comments many times over the years) I would challenge that assertion. It takes significantly more time to find and read sloppy “bad practice” code than it does when working with clean code. These practices will also help to reduce bugs and your code will be appreciated by everyone that reads it.

When we’re talking about programming languages, you can pretty much write statements as you see fit and the code will execute. Obviously there are some exceptions to this (such as indentations in Python) but that’s for another day. What this means is that when I’m talking about SQL best practices, one could make the argument that the this is just a matter of opinion and writing style.

How you write it matters. Think about a book for example. Sure, you could write an entire book without paragraphs, line returns, standard spacing between sentences, and more. Yes, it would still be a book and yes, the reader could probably understand it. However, how easily would it be for you find a specific section if there weren’t paragraphs? How easy would it be to have confidence that you’re actually in the right section if you were asked to edit something? For these reasons any many more, I’m outlining some best practices.

Please note that while all of these practices could be considered “opinions”, they are practices that I’ve used after 20 years of making mistakes, challenges with efficiently reading, editing, and understanding code, and watching my team members face the same challenges. I’ll try my best to tell you about the practice and the logic behind using them. I’ll leave it up to you to consider these practices, see what works for you, and what works for your team.

Formatting

The first thing that I’d like to talk about is formatting. Code should be well formatted and visually appealing, which makes it very easy to read. Having properly formatted code we pay off when it comes to debugging, troubleshooting, and modifying your code.

When it comes to formatting, there are a number of things that should be considered such as intentions, alignment, comma positions, and text case. If you made it this far in the reading you’ve probably noticed how well aligned my code is and that most items are found on a single line. This make a tremendous difference in readability.

Below is an example of code that has been written and is quite unreadable. In this example you’ll notice a number of things go against best practices such as:

• number of items per line
• alignment issues
• trailing commas
• poor or lack of aliasing
• lack of comments
• grouping by number instead of name
• position of aggregate functions in select statement
• multiple hard to detect bugs

In this article I’ll discuss these practices and more to help you write clean and bug-free code that you can be proud of.

Bad Practices — Many Issues

Best Practices

Look at the code below and compare to the code above. Which one is easier to read? Which one provides the best context about what the intent of the code is and what some of the conditional values mean? Which version provides a cleaner UI that will allow you to quickly spot bugs or avoid bugs all together? I’m hoping that you’ve agreed that the code below serves to overcome all of the challenges found in the above code.

The counter argument that I’ve heard people say is, “Well, you had to write more lines of code.” This is irrelevant. The computer doesn’t care and it’s a couple of key strokes to have a line return and spaces or tabs.

In the following sections I will discuss each of these issues and provide examples of the good and bad practices.

Continue learning more

For an in-depth analysis on the bad practices and the best practices, check out my course on SQL Best Practices