SQL Style Guide

Why?

Why should we even be talking about a SQL Style Guide?

My first reason is illustrated by the following snippet of code (the full script is over 600 lines), which is not much easier to read even when the font is a sensible size.

Code written like this is hard work. I am often asked to improve or debug such code. The developer has obviously worked hard on this and has produced something that gives plausible answers and maybe correct answers. But it was much more difficult than it should have been, and it will be difficult next time it needs tweaking by the original developer or someone else.

What is wrong with this?

• Capitalisation is inconsistent. At the beginning we have "CH.CTYPE_CODE"; toward the end we have "CH.ctype_code". Sometimes a word appears all in uppercase, sometimes all in lower case, sometimes with the first letter in uppercase and the rest lowercase. This applies to 22 words in this section of the query: column names, schema names, function names and keywords.
• Some lines are commented out. This usually implies that the developer was not confident about his changes. Before the final test run (with all the tests) the comments with redundant code must be removed.
• The first item in a list appears on the same line as the word introducing the list, and the rest are not aligned with this or with one-another
• commas in a multi-line list appear at the beginning of the line. This is common practice but it is not a good idea. They should appear at the end of the previous line.
• elements in multi-line lists are sometimes aligned and sometimes not
• table names are given aliases made from the first letter in each word in the table name. This is common practice but it is not a good idea
• codes are arbitrarily translated into other codes, using case statements. If this regrouping is necessary for reporting then it should be done in a way that gives consistency in all reports. Using case statements to this extent in the main query is guaranteed to give inconsistent reports as other developers (or even the same developer) copy and paste blocks of code into other queries and inevitably tweak the conditions

This code just screams, "Programming By Coincidence". (See The Pragmatic Programmer, page 197)

Simply formatting SQL code consistently helps understanding, while it is being written and later when it is being enhanced. Consistent formatting also helps us to spot errors, from commas in the wrong place to missing text that closes a block (such as ")" or "end" or a single quotation mark). The person who fixes the code in six months or a year from now may be one of your buddies, or maybe you. Do yourself and your friends a favour by making your code easy to read.

What Are We Trying To Achieve?

• Accuracy: Our queries must give correct answers. When we change data in the database, we want our SQL statements to change all the data it should, none of the data it should not change, and make exactly the intended change, no more, no less.
• Productivity: We want each SQL developer to do as much useful work as can be fitted into a sustainable work pattern, and to spend as little time as possible on time-wasting tasks.
• Reusability: When one SQL developer has written a useful piece of SQL and tested it to make sure that it returns correct results, we may want others to be able to copy it and adapt it to their own needs.
• Upgradablility

How To Help Your Co-workers, and Yourself

Do Not Use Tabs

Do not use tabs. Unfortunately many tools for preparing and submitting SQL statements put tabs in by default, and many tools that might be used for viewing and printing SQL scripts have default tab stops every 8 characters. This means that your SQL ends up on the right hand side of the page, meaning that the page becomes wider and you have to scroll from side to side to read it. This does not make it easy to understand the script.

In SQL scripts the tab character only makes them more difficult to read. In Python, they create havoc. In many cases the Python interpreter detects that something is wrong and gives a "TabError". I know that Python is a very popular language, but I will never use something that is so fragile. And what is the point of relying on indentation? To save typing "{" and "}" or "begin" and "end"? That is a very false economy.

Do Use spaces To Indent Your SQL Code

Indentation is a great aid to understanding, if it is applied consistently and if the indent is 2, 3 or 4 space characters. I use 4 characters, but I'm happy to read SQL with a consistent indent of 2 or 3 spaces too.

When given a complex SQL script indented with tabs, the first thing I do is to replace every tab with four spaces. I then have to re-align some code, especially where it has been indented with a mixture of tabs and spaces. I wish the tab character had never been carried forward from the typewriter to the computer.

Do Not Use A Tool That Writes Code For You

The advertising for one of these tools states that it enables you to write SQL code "without expertise". That's true, it does. However it does not help you to write code that gives correct results. You can do that, but the variety, complexity and inconsistency of real-world databases is way beyond the amoeba-level intelligence in these tools. Letting one of these tools write your code is even worse than the "programming by coincidence" described by Andy Hunt and Dave Thomas in The Pragmatic Programmer.

Do Use A Good Text Editor

Do use a good text editor. I use TextEdit on Linux and I used to use TextMate (Mac only), which does many useful things with SQL, HTML, XML, etc. There are other products available: NotePad++ is a free Windows tool that does good job, Sublime Text is available at a reasonable price for Windows and Mac.

A good text editor understands the syntax of the file you are editing, highlighting the code in different colours. Particularly helpful is the colouring of comments and literals so that you can see if you have failed to terminate each element properly. Once you have a tool like one of these, you will appreciate the help it gives you for SQL coding, as well as XML, XHTML, shell scripts, markup, markdown and, almost certainly, your favourite programming language.

Do Not Be Afraid Of Newlines

For many SQL coders it seems that their primary objective is to minimise the number of newlines. Why? Newlines don't cost anything. Well, a newline costs the same as a space on sensible systems (UNIX and Mac). On Windows a newline costs the same as a space in keystrokes and the same as two spaces in memory and on disk. That is poor design as far as Windows is concerned but it isn't going to break the bank.

Newlines can help a lot with making your SQL understandable and that is a good objective.

    select
        claimant_id,
        to_char(created_at, 'YYYY-MM') as payment_month,
        sum(amount)                    as total_paid_out
    from
        transaction
    where
        actual_or_reserve = 'Actual' and
        payment_or_recovery = 'Payment'
    group by
        claimant_id,
        payment_month
    order by
        claimant_id,
        payment_month;

In this example the main SQL clauses stand out clearly (even without syntax-highlighting. They are not indented and there is nothing else on the same line as these clauses. Often people feel obliged to put the first element after the clause on the same line and then align any following elements with that, like this

    select claimant_id,
           to_char(created_at, 'YYYY-MM') as payment_month,
           sum(amount)                    as total_paid_out
    from transaction
    where actual_or_reserve = 'Actual' and
          payment_or_recovery = 'Payment'
    group by claimant_id,
             payment_month
    order by claimant_id,
             payment_month;

Is that easier to read? I don't think so.

Write your code in lower case

The documentation of most, possibly all, SQL databases shows the keywords in capital letters with table names and column names in lower case. This is because they are explaining the syntax, the documentation is in black-and-white, and upper case is a way to show what the keywords are and what tokens you will fill in to write a real query. You do not have to copy this convention for your real SQL statements. Lower case is easier to read and easier to type. Upper case is shouty. Also, almost no-one sticks to the convention of writing keyword in upper case. Look at the snippet of code at the start of this webpage. It has some keywords in upper case, some in lower case and some in mixed case. That is not helpful.

if you use a good text editor, it will automatically highlight keywords and literals for you, like this:

Ditch the upper case. Write your code in lowercase to make it easier to type and easier to read.

Creating Tables

Give every table a primary key.
Always use a surrogate key.
Always call it id.
Always make it the first column.
Almost always make it a generated number (serial, identity, auto-increment).
If your table is going to have less than 2 billion rows, make the key an integer (4 bytes); if it is going to have more than 2 billion rows make the key a big integer (8 bytes, sometimes called a "long"). If you are using Oracle, make the key number(9,0) or number(18,0).

Creating Views

Don't! Views are a bad idea.

Many people are shocked when I say this, so let me explain why. Views are used for three things in my experience:

• To try to insulate parts of an application from changes.
  
  This was one of the early selling points. You make some changes in the database to support some new application functionality, and then you write a view to make the new data look the way it did before. Please don't do this. You should change all the affected parts of the application to match the current required behaviour. To have two versions of your database operating at the same time is just asking for trouble; you are adding complexity unnecessarily. It is technical debt that you will have to repay sometime, and we know that debt has a cost that gets higher the longer you leave it. Keep your code clean and do not use views as a rug to brush the dust under.
  
  
• To "save time" by using the joins and case statements that someone else has written.
  
  People look for a view that has the columns they need for a particular report, and they use that view. They don't check what the view does. It may restrict the data in some way; it may translate different cases into a single case; it may introduce dummy values; it may use outer joins to hide the fact that some data is incomplete. Such views may be horribly inefficient, accessing parts of the database that are irrelevant to your query. Worst of all the view may be layered on top of other views, multiplying the corruption of the real data and introducing more and more inefficiency.
  
  
• To implement data transformation.
  
  Soon after people have spent a fortune on a Data Integration, or ETL, or ELT tool, they realise that it actually makes life much more complicated than the tasks of understanding your database and writing SQL to access it. So they put the transformation into SQL in a view and use that as the input to the tool. The tool, with its fancy diagrams, and "intuitive" approach (that's what the salesman says anyway) then reads the view and you don't have to bother with so much of the torturous process of trying to write logic by drawing diagrams. The expensive tool then becomes a very poor orchestration tool.