Thursday, August 5, 2010

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:

Listing 1

select c.customer_name, o.order_amt,
d.qty from customers c left outer join
orders o on c.customer_nbr = o.customer_nbr
left outer join orderdetails d on d.order_nbr =
o.order_nbr and d.sku = 101

Or maybe like this:

Listing 2

SELECT C.CUSTOMER_NAME, O.ORDER_AMT,
D.QTY FROM CUSTOMERS C LEFT OUTER JOIN
ORDERS O ON C.CUSTOMER_NBR = O.CUSTOMER_NBR
LEFT OUTER JOIN ORDERDETAILS D ON D.ORDER_NBR =
O.ORDER_NBR AND D.SKU = 101

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.

Contrast
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:

Listing 3

SELECT C.customer_name...

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.

Repetition
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.

Listing 4

SELECT C.customer_name... FROM Customers AS C...

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.

Alignment
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:

Listing 5

SELECT C.customer_name...
FROM Customers AS C...

Alignment creates a clean and pleasing look of the code structure.

Proximity
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.

Listing 6

SELECT C.customer_name, O.order_amt...
FROM Customers AS C
LEFT OUTER JOIN Orders AS O...

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:

Listing 7

SELECT C.customer_name, O.order_amt, D.qty
FROM Customers AS C
LEFT OUTER JOIN Orders AS O
 
ON C.customer_nbr = O.customer_nbr
LEFT OUTER JOIN OrderDetails AS D
 
ON D.order_nbr = O.order_nbr
 AND D.sku = 101;

I added a couple extra styling elements (compared to the original query), can you catch them?

Another form of alignment is this:

Listing 8

         SELECT C.customer_name, O.order_amt, D.qty
          
FROM Customers AS C
LEFT OUTER JOIN Orders AS O
            
ON C.customer_nbr = O.customer_nbr
LEFT OUTER JOIN OrderDetails AS D
            
ON D.order_nbr = O.order_nbr
           
AND D.sku = 101;

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!

9 comments:

Jeff W8 said...

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...

Tony R said...

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!

Kent Waldrop said...

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.

Kent Waldrop

Anonymous said...

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.

Anonymous said...

Couldn't agree more, Plamen!

Mark Freeman said...

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.

Manoj Pandey said...

I like the style#7 but its time consuming, so I follow the style#6.

Anonymous said...

Are you telling me my code needs to look like CRAP? :)

lee woo said...

No matter what you're going through, there's a light at the end of the tunnel and it may seem hard to get to it but you can do it and just keep working towards it and you'll find the positive side of things. See the link below for more info.


#matter
www.matreyastudios.com