Editorials

SQL Readability

One of the things I have found helpful is to be consistent on how your layout your SQL queries. Consistency is probably more important than any particular little option. If your queries are consistent throughout, then others looking at your code will be able to understand more quickly what is going on. In this area, I really do like having a basic SHOP standard for layout.

I don’t like standards just to force everyone to do the same thing the same way every time, without room for either personal preference or edge case optimizations. If one developer prefers to use a Common Table Expression (CTE) and another developer prefers to use an Inline view, the net result is the same.

But there are a few things I have found worthy of standardization. These are personal preferences you can assess as you see fit. I’ll provide my logic, knowing that it doesn’t work for everyone else.

I like to capitalize ALL of the SQL Commands and operation key words. This makes things stand out.

SELECT, FROM, INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, ON, WHERE, IN, LIKE, OVER, PARTITION BY, ORDER BY, SUM, MAX, MIN, TOP, etc. If you capitalize all of these key words they tend to stick out as what they are, allowing the logic of your query to be distinguished through the lack of capitalization.

I like to left align keywords…

SELECT …
FROM    …
JOIN      …
WHERE ….
GROUP BY …
HAVING

All of the other contents are indented beneath the parent Key Word.

If I am selecting multiple columns I like to put each column on a separate line, preceding the column name with a comma. This way, if I want to comment out a column I can simply comment the single line, instead of having to impact at trailing comma from the previous line. This is VERY MUCH a personal preference. You can apply a similar construct to your where clause.

This tip will not make you a better programmer, or help you write better SQL. What you will gain is the ability to communicate your SQL Queries clearly to other people who have not been working with your code.

Cheers,

Ben

Facebooktwittergoogle_plusredditpinterestlinkedinmail
  • Maurice Pelchat

    Good start, this is what I do myself. However, keyword capitalization is unnecessary if someone configure SSMS to colorize them differently. This recommendation is however useful for those who are using dev tools not able of keyword colorization.

    I would add that everything under a From is indented by 2 chars relative to the FROM keyword alignment, because they are statically dependent of the From keyword. Obvioulsy I follow your recommandation for the same level matching WHERE, GROUP BY, HAVING.

    I allow myselft to write expressions in parenthesis in a single line if they are quite short (fitting in editing window).

    If they are too long they are written aligned under the opening parenthesis, and closing parenthesis is aligned with the opening parenthesis (on the line below). The opening parenthesis is aligned below the operator/function name aligned with the start of operator/function.

    General rule of thumb in SQL formatting: SQL have better readability written on many lines. SQL statements are often long, and its syntax elements make often more sense on separate lines, that mixing many of them on the same line.

    For example, I tend to format Joins this way dividing each JOIN block from the next by an empty line. JOINs are very important clause in queries, and make sense query navigation so it is important to have a quick look at them, in query path progression. I thing this helps to visualize it easily.

    JOIN
    table Name as Alias
    ON … join conditions on one or many lines

    JOIN
    NextTableName as Alias
    ON … join conditions on one or many lines

    • Ben

      Seems like we have a similar mindset. I’m guessing that’s why I have been able to follow queries I’ve seen that you have written. In fact, that makes the case for at least a minimum bit of standardization.

      Thanks for the feedback