What SQL Coding Standard Do You Follow

What SQL coding standard do you follow?

Wouldn't call it coding standard - more like coding style

SELECT
    T1.col1,
    T1.col2,
    T2.col3
FROM
    table1 T1
    INNER JOIN ON Table2 T2 ON T1.ID = T2.ID
WHERE
    T1.col1 = 'xxx'
    AND T2.Col3 = 'yyy'

• capitalize reserved words
• main keywords on new line
• can't get used to commas before columns
• always use short meaningful table aliases
• prefix views with v
• prefix stored procs with sp (however don't use "sp_" which is reserved for built in procs)
• don't prefix tables
• table names singular

Are there any published coding style guidelines for SQL?

Since asking this question I have written a public SQL style guide that is compatible with Joe Celko's SQL Programming Style book under the Creative Commons Attribution-ShareAlike licence.

It is available over at www.sqlstyle.guide or as markdown directly from the GitHub repo.

SQL formatting standards

I am of the opinion that so long as you can read the source code easily, the formatting is secondary. So long as this objective is achieved, there are a number of good layout styles that can be adopted.

The only other aspect that is important to me is that whatever coding layout/style you choose to adopt in your shop, ensure that it is consistently used by all coders.

Just for your reference, here is how I would present the example you provided, just my layout preference. Of particular note, the ON clause is on the same line as the join, only the primary join condition is listed in the join (i.e. the key match) and other conditions are moved to the where clause.

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

One tip, get yourself a copy of SQL Prompt from Red Gate. You can customise the tool to use your desired layout preferences, and then the coders in your shop can all use it to ensure the same coding standards are being adopted by everyone.

How to make sure that my SQL code is not a scary mess

You could try checking out Joe Celko's book SQL Programming Style. I'm sure that there are a lot of people who disagree with his style, but it's a good start.

Some of my own "rules"

• SQL keywords are always all upper-case
• Table names are "proper" case, while columns and variables are all lower-case
• Each "major" clause in a statement is at the start of a line
• JOIN and WHERE criteria appear beneath and are indented and aligned
• Nested items are indented further
• I use aliases for all tables and views

For example:

SELECT
    column_1,
    column_2,
    CASE
        WHEN column_5 = 'Blah' THEN 1
        WHEN column_6 = 'Blah' THEN 2
        ELSE 3
    END AS column_alias
FROM
    My_Table MT
INNER JOIN My_Other_Table MOT ON
    MOT.column_1 = MT.column_1
WHERE
    MT.column_2 = 'Some Value' AND
    (
        MT.column_3 = 'Some other value' OR
        MT.column_4 = 'Some other value'
    )

Resources for SQL Server code review and best practices

Check out this excellent resource:

SSW Rules to Better SQL Server Databases

This is also good, although some of the advice may have changed since the article dates from 2001):

SQL Server TSQL Coding Conventions, Best Practices, and Programming Guidelines

Coding standards and line length

There is a pattern you can see in each example - they are indented to the first parameter of the function. This is a good standard to follow as it transposes the data from horizontal to vertical and the columns allow easy reading.

For other line length issues, such as lengthy computations, the preferred method is to break it down. Calculating the julian date, or easter is done in several steps instead of one long calculation.

SQL Statement indentation good practice

SELECT column1
     , column2
FROM table1
WHERE column3 IN
(
    SELECT TOP(1) column4
    FROM table2
    INNER JOIN table3
    ON table2.column1 = table3.column1
)

I like to have all "," in front, this way I never search them when an error at line X from the SQL editor.

---

This is an example for those who do not use this type of writting SQL statement. Both contain an error of a missing comma.

SELECT sdcolumn123
 , dscolumn234
 , sdcolumn343
 , ffcolumn434
 , sdcolumn543
 , bvcolumn645
  vccolumn754
 , cccolumn834
 , vvcolumn954
 , cvcolumn104
FROM table1
WHERE column3 IN
(
    ...
)

SELECT sdcolumn123, dscolumn234, asdcolumn345, dscolumn456, ascolumn554, gfcolumn645 sdcolumn754, fdcolumn845, sdcolumn954, fdcolumn1054
FROM table1
WHERE column3 IN
(
    ...
)

I found easier and more quick at the first example. Hope this example show you more my point of view.

---

Related Topics