It’s a Matter of Style
W riting SQL can be very enjoyable activity. Reading SQL can be also enjoyable (maybe like reading poetry to some), or very unpleasant… How do you write SQL with style that results in eye pleasing and easy to read/understand code? And does it matter?
Sometimes code writing drills down to concentrating on the task at hand and producing a brilliant piece of code, which looks like this:
Or maybe like this:
While this code performs exceptionally and solves the problem in a very clever way, is it really that good? What happens when the code review/test team gets their turn? Or when you/someone else has to modify it two years from now? To my opinion this code is a very long way from what a real production code should be. And yes, this is very real and it happens every day, even as I type this. Just pay attention on the next code review, or take a look at any online SQL forum (and no, it is not only the people that ask questions, unfortunately many SQL gurus that know it all would post an answer with similar “example” style).
How do you make this code look better? The answer is in the four basic principles of design: contrast, repetition, alignment, and proximity. Let’s look how applying these principles of design (which many think are applicable only to graphic design) can lead to stylish and enjoyable code.
The idea is to use contrast for elements that a very different. One example is columns and reserved keyword. They are not the same and the code should make that distinction very clear. Let’s apply that:
Here the SELECT keyword is capitalized to differentiate from the lower case column name. Also, the table alias is capitalized to indicate clearly the table source.
Repeating the same element styles for all similar items adds consistency and organization throughout code. For example, repeat and maintain capitalization for all keyword, do not mix style in different context of the code. Like the style of the SELECT and FROM reserved keywords in Listing 4.
This allows to “visualize” the shape of the query code. Now the eye can easily flow from one section of code to the next one and concentrate on each element.
Code elements should not be placed arbitrarily on the lines of code. Every code item should have some visual connection with another item in the code. One example is aligning the start of each clause of the query (SELECT, FROM, WHERE, etc.) on a new line:
Alignment creates a clean and pleasing look of the code structure.
Code items that relate to each other should be grouped close together. When several items are in close proximity they become one visual unit. Like placing SELECT and column names together on the line, similar for FROM and table names or WHERE and predicates. Listing 6 demonstrates this.
This makes the code structure very clear and eliminates clutter.
Let’s apply all four principles to the initial query. Here is one way it may look:
I added a couple extra styling elements (compared to the original query), can you catch them?
Another form of alignment is this:
There are many different ways to style your SQL. You may agree or disagree with some elements, but the bottom line is this: style matters!
I completely agree. I found that I sometimes have to restyle someone else's code just to get my head around it. Of course, the dark side of being really picky is that you sometimes end up goong nuts, just because you can't stand someone else'xs style…
You really begin to realize the importance of readability and style once you start digging through someone's old 500 line stored procedure that looks like it's been ran through a blender. It makes them that much harder to try and understand and eats up significant amounts of time fixing formatting.
There are some really nice tools to do formatting clean-up for you (but they can be a bit pricey). I use the SQL Pretty Printer SSMS plug-in which is totally worth it if you need clean up formatting regularly with existing SQL.
Plamen by the way just wanted to chime in and say great talk a couple of months back at the Jacksonville SQL Saturday!
Thank you for the article, Plamen. So much code that I see comes through looking like the problem code you started with. Style makes a difference in the quality and the perception of quality of SQL.
You forgot to mention that ctrl+shift+u makes code upper case (for Microsoft SQL) and ctrl+shift+l makes code lower case. I was told to write my SELECT and FROM always in Upper case.
Couldn't agree more, Plamen!
I agree, and think it is even more of an issue for functions, procedures, and triggers than just for SQL statements.
I prefer to indent the joins to make the FROM effectively "hang out" on the left like the SELECT, WHERE, and other "major" clauses.
Regardless, consistency is important.
I like the style#7 but its time consuming, so I follow the style#6.