Formatting

Formatting your code is just good practice. It matters less how you format your code than having a consistent appearance. I am using ‘formatting’ pretty loosely by including some general coding practices.

Recognizing that formatting SQL is largely preference, here is how I like it and why.


1. Keywords should be lowercase.

Many people go with uppercase; I find it distracting.

SELECT * FROM Widgets WHERE Quantity >= 20;

Looking at the above statement, we see ‘SELECT FROM WHERE!’ The heart of it is dwarfed by the keywords.


2. Use whitespace.

In simple examples, this seems tedious, but the point is that we demarcate the sections; we can easily see where each part of the statement is and what belongs to it.

select
    *
from
    Widget
where
    Quantity >= 20; 

Simple statements, we can overlook this. In a procedure, putting a singleton select or any obvious statement on one line with a comment immediately above it is fine as long as the statement is not overly complex.

-- Get the widget name
select Widget into xWidget from Widget where WidgetID = 168; 

I am old school, so I miss table joins in the where clause; the reason being formatting. The formatting looks awkward with joins in the from clause since there is no good way to get all of the pieces to line up.

Example 1a:

select
    a.x,
    a.y,
    b.c
from
    a
    inner join b
        on b.a = a.a
where
    a.c = 1; 

Example 1b:

select
    a.x,
    a.y,
    b.c
from
    a,
    b
where
    b.a = a.a

    and a.c = 1; 

Depending on what tools you are using, you may have some whitespace sensitivity issues (which really makes me angry). But the second statement reads easier to me. I immediately see that there are two tables, and one join.

The first statement is a bit cumbersome. I see the inner join and the on, so I assume that they are joined properly. It is really just the formatting that I do not care for. You could go with:

from
    a inner join
    b
        on b.a = a.a

The annoyance being that as soon as you alias your tables, it decreases the readability. The nice part of joining in the from clause is that it reduces the likelihood of Cartesian products. I am not condoning performing joins in the where clause, just mentioning that I prefer everything simpler (lines up better that way).


3. Alias tables

Meaningful tablenames segue into easy aliases. I can see using ‘a,’ ‘b’ and ‘c’ in examples, but in the real world they make it more complicated than not aliasing. You do not arbitrarily name tables—treat aliases the same.

select
    m.Manufacturer,
    p.Product + ‘ ‘ + p.Size Product,
    q.Quantity
from
    Manufacturer m,
    Product p,
    Quantity q
where
    p.ManufacturerID = m.ManufacturerID
    and q.ProductID = q.ProductID;

4. Use comments 

By and large, I think that code is self-documenting. I prefer block comments (‘/* … */’) for ideas, or logical separations. I use inline comments (‘-- …’) to describe a statement. I comments statements only if they do not read easily. If you are nesting a few functions, or a non-intuitive clause, comment it. I consider conditional operations a logical idea and typically comment them.

-- Number of whole weeks times five days each
oDay := ((floor((xEnd - xStart) / 7)) * 5);

-- Add difference in days outside of whole weeks
oDay := oDay + mod((xEnd - xStart), 7);

5. Use Blocks

Parentheses and begin/end allow you to show your intentions. Much like whitespace, it delineates your ideas, and is often syntactically required.

At a minimum, anytime I use an OR clause, I put parentheses around my idea to show that the condition was deliberate.


I believe that those are my top preferences. Take the ACID properties beyond transactions. What are your thoughts?