SQL formatting standards

Question

In my last job, we worked on a very database-heavy application, and I developed some formatting standards so that we would all write SQL with a common layout. We also developed coding standards, but these are more platform-specific so I'll not go into them here.

I'm interested to know what other people use for SQL formatting standards. Unlike most other coding environments, I haven't found much of a consensus online for them.

To cover the main query types:

select
    ST.ColumnName1,
    JT.ColumnName2,
    SJT.ColumnName3
from 
    SourceTable ST
inner join JoinTable JT
    on JT.SourceTableID = ST.SourceTableID
inner join SecondJoinTable SJT
    on ST.SourceTableID = SJT.SourceTableID
    and JT.Column3 = SJT.Column4
where
    ST.SourceTableID = X
    and JT.ColumnName3 = Y

There was some disagreement about line feeds after select, from and where. The intention on the select line is to allow other operators such as "top X" without altering the layout. Following on from that, simply keeping a consistent line feed after the key query elements seemed to result in a good level of readability.

Dropping the linefeed after the from and where would be an understandable revision. However, in queries such as the update below, we see that the line feed after the where gives us good column alignment. Similarly, a linefeed after group by or order by keeps our column layouts clear and easy to read.

update
    TargetTable
set
    ColumnName1 = @value,
    ColumnName2 = @value2
where
    Condition1 = @test

Finally, an insert:

insert into TargetTable (
    ColumnName1,
    ColumnName2,
    ColumnName3
) values (
    @value1,
    @value2,
    @value3
)

For the most part, these don't deviate that far from the way MS SQL Server Managements Studio / query analyser write out SQL, however they do differ.

I look forward to seeing whether there is any consensus in the Stack Overflow community on this topic. I'm constantly amazed how many developers can follow standard formatting for other languages and suddenly go so random when hitting SQL.

Answer 1

I tend to use a layout similar to yours, although I even go a few steps further, e.g.:

select
        ST.ColumnName1
    ,   JT.ColumnName2
    ,   SJT.ColumnName3
from
                SourceTable     ST

    inner join  JoinTable       JT
        on  JT.SourceTableID    =   ST.SourceTableID

    inner join  SecondJoinTable SJT
        on  ST.SourceTableID    =   SJT.SourceTableID

where
        ST.SourceTableID    =   X
    and JT.ColumnName3      =   Y
    and JT.Column3          =   SJT.Column4

Perhaps it looks a little over the top at first, but IMHO the use of tabulation in this way gives the cleanest, most systematic layout given the declarative nature of SQL.

You'll probably end up with all sorts of answers here. In the end, it's down to personal or team-agreed preferences.

Answer 2

There are many good points in this thread. The one standard that I have been attempting to convince people to use is placing the comma on the same line before each column. Like so:

Select column1
   ,column2
   ,column3
   ,column4
   ,Column5 ...ect

Opposed to:

Select column1,
   column2,
   column3, ect...

The reason I prefer this practice is because, if necessary, you can comment out a line and there wont be a comma issue when you run it on account of the corresponding comma being commented out as well. I know that I saw another user in the thread that had done this as well but did not really point it out. Not a huge revelation to bring to the conversation but my two cents. Thanks

Answer 3

My preferred style:

SELECT
  ST.ColumnName1,
  JT.ColumnName2,
  SJT.ColumnName3
FROM
  SourceTable ST
INNER JOIN
  JoinTable JT
ON
  JT.SourceTableID = ST.SourceTableID
INNER JOIN
  SecondJoinTable SJT
ON
  ST.SourceTableID = SJT.SourceTableID
WHERE
  ST.SourceTableID = X
AND
  JT.ColumnName3 = Y
AND
  JT.Column3 = SJT.Column4

Answer 4

I realise I am very late to this debate, but I would like to give my thoughts. I am definitely in favour of commas at the start of the line. Like you say Adam Ralph, it's easier to comment out a field and I also find it is more difficult to miss out a comma accidently when they are at the beginning, whilst this doesn't sound like a major issue. I have spent hours in the past trying to track down accidental syntax errors in lengthy T-SQL procedures, where I have accidently missed out a comma at the end of the line (I'm sure some of you have probably done this as well). I'm also in favour of aliasing as much as possible.

Overall, though, I realise it's all down to personal preference, what works for some doesn't for others. As long as you can read the code easily and each developer shows some consistency in their style throughout, I think that's most important.

Answer 5

I like:

SELECT ST.ColumnName1, JT.ColumnName2, SJT.ColumnName3 --leave all selected columns on the same line
FROM 
    SourceTable ST
INNER JOIN JoinTable JT ON JT.SourceTableID = ST.SourceTableID
INNER JOIN SecondJoinTable SJT --only splitting lines when more than 1 condition
    ON ST.SourceTableID = SJT.SourceTableID
    AND JT.Column3 = SJT.Column4
WHERE
    ST.SourceTableID = X
    and JT.ColumnName3 = Y

to get more code in a smaller viewing area. I also beleive that keywords should be in capitals

Answer 6

It appears that most of you still work on monitors that only support 800x600. My monitors will do 1920x1080, so I want to use up all that space to the right.

how about this:

select col1, col2, col3
, case when x = 1 then 'answer1'
       else 'answer2'
  end
, col4, col5, col6, col7
from table1 t1
inner join table2 t2 on t1.col1 = t2.col1 and t1.col2 and t2.col2
where t1.col5 = 19 and t1.col7 = 'Bill Gates'