SQL Formatting Standards

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.

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

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'
    )

Visual Studio Code vs SQL Formatting

I hope this will be of some help to you.

If I understood you correctly, you can use first this shortcut:

Win: Ctrl+Shift+P / Mac: Cmd+Shift+p

And then type "Change Language Mode".

Also try and visit this link for additional info:
https://marketplace.visualstudio.com/items?itemName=cymonk.sql-formatter

Also you can visit this link:
https://code.visualstudio.com/docs/languages/overview
where you can find additional info about that, for example:

Changing the language for the selected file
In VS Code, we default the
language support for a file based on its filename extension. However,
at times you may wish to change language modes, to do this click on
the language indicator - which is located on the right hand of the
Status Bar. This will bring up the Select Language Mode drop-down
where you can select another language for the current file.
Tip: You can get the same drop-down by running the Change Language
Mode command (Ctrl+K M).

Formatting Clear and readable SQL queries

With large queries I tend to rely a lot on named result sets using WITH. This allows to define the result set beforehand and it makes the main query simpler. Named results sets may help to make the query plan more efficient as well e.g. postgres stores the result set in a temporary table.

Example:

WITH 
  cubed_data AS (
     SELECT 
        dimension1_id,
        dimension2_id,
        dimension3_id,
        measure_id,
        SUM(value) value
     FROM
        source_data
     GROUP BY
        CUBE(dimension1, dimension2, dimension3),
        measure
  ), 
  dimension1_label AS(
     SELECT 
        dimension1_id,
        dimension1_label
     FROM 
        labels 
     WHERE 
        object = 'dimension1'
  ), ...
SELECT 
  *
FROM  
  cubed_data
  JOIN dimension1_label USING (dimension1_id)
  JOIN dimension2_label USING (dimension2_id)
  JOIN dimension3_label USING (dimension3_id)
  JOIN measure_label USING (measure_id)

The example is a bit contrived but I hope it shows the increase in clarity compared to inline subqueries. Named result sets have been a great help for me when I've been preparing data for OLAP use. Named results sets are also must if you have/want to create recursive queries.

WITH works at least on current versions of Postgres, Oracle and SQL Server

Apply SQL Standards to script

Change ISNULL to COALESCE and square brackets to " and then it validates.

SELECT a.UserAccountKey,
       SUM(COALESCE(b."measure Y", 0.0)) AS "measure Y",
       SUM(COALESCE(c."measure Z", 0.0)) AS "measure Z"
FROM   XXX a
       LEFT OUTER JOIN YYYY b
         ON a.UserAccountKey = b.UserAccountKey
       LEFT OUTER JOIN ZZZZ c
         ON a.UserAccountKey = c.UserAccountKey
GROUP  BY a.UserAccountKey;

This does mean that you need to ensure that QUOTED_IDENTIFIER is ON in SQL Server.

SQL Formatting Standards