Skip to content

Instantly share code, notes, and snippets.

@mattmc3

mattmc3/modern_sql_style_guide.md

Last active October 14, 2025 08:17
Show Gist options
  • Save mattmc3/38a85e6a4ca1093816c08d4815fbebfb to your computer and use it in GitHub Desktop.
Save mattmc3/38a85e6a4ca1093816c08d4815fbebfb to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. mattmc3 revised this gist Jan 17, 2019. 1 changed file with 4 additions and 4 deletions.
    8 changes: 4 additions & 4 deletions modern_sql_style_guide.md
    View file
    Original file line number Diff line number Diff line change
    @@ -2,8 +2,8 @@
    layout: default
    author: mattmc3
    title: Modern SQL Style Guide
    revision: 2018-12-18
    version: 1.0.0
    revision: 2019-01-17
    version: 1.0.1
    description: A guide to writing clean, clear, and consistent SQL.
    ---

    @@ -142,7 +142,7 @@ for modern SQL development. Using lowercase keywords is preferred because:
    [harder to read][shoutcase-typeography].
    * SQL syntax is not case-sensitive, and thus lowercase keywords work correctly
    in all variants of SQL
    * No other modern languages since BASIC use ALLCAPS keywords.
    * No other modern languages use ALLCAPS keywords.
    * Modern editors color code SQL keywords, so there is not a need to distinguish
    keywords by casing.
    * If you are in an environment where your keywords are not colored (i.e. as a
    @@ -176,7 +176,7 @@ sake of consistency rather than mixing styles.
    * Tables with semantic prefixes are okay if they aid understanding the nature
    of a table (e.g. in a Data Warehouse where it is common to use prefixes like
    `Dim` and `Fact`).
    * Never give a table the same name as one of its columns and vice versa.
    * Avoid giving a table the same name as one of its columns.
    * Use a joining word for many-to-many joining tables (cross references) rather
    than concatenating table names (e.g. `Xref`):

  2. mattmc3 revised this gist Dec 19, 2018. 1 changed file with 51 additions and 46 deletions.
    97 changes: 51 additions & 46 deletions modern_sql_style_guide.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,25 +1,26 @@
    ---
    layout: default
    author: mattmc3
    title: Modern SQL Style Guide
    revision: 2018-12-18
    version: 0.1.0-beta
    version: 1.0.0
    description: A guide to writing clean, clear, and consistent SQL.
    ---

    # Modern SQL Style Guide

    ```sql
    select *
    from modern.sql_style_guide guide
    where guide.attributes in ('clean', 'easy', 'sensible')
    from modern.sql_style_guide as guide
    where guide.attributes in ('clean', 'clear', 'consistent')
    and guide.look = 'beautiful'
    ```

    ## Purpose

    These guidelines are designed to make SQL statements easy to write, easy to
    read, easy to maintain, and beautiful to boot. This document is to be used as a
    guide for anyone who would like to write clean and clear SQL code.
    read, easy to maintain, and beautiful to see. This document is to be used as a
    guide for anyone who would like to codify a team's preferred SQL style.

    This guide is opinionated in some areas and relaxed in others. You can use this
    set of guidelines, fork them, or make your own - the key here is that you pick a
    @@ -29,16 +30,17 @@ is a guiding principle to achieve cohesion.
    It is easy to include this guide in Markdown format as a part of a project's
    code base or reference it here for anyone on the project to freely read.

    The original Modern SQL style guide [homepage is here][homepage] and is
    This guide is based on various existing attempts at SQL standards including:
    [http://www.sqlstyle.guide][sqlstyleguide] and
    [Kickstarter guide][kickstarter-sql-guide]. Due to its origins, it is
    licensed under a [Creative Commons Attribution-ShareAlike 4.0 International
    License][license].

    Based on various existing attempts at SQL standards including:
    [http://www.sqlstyle.guide][sqlstyleguide] and
    [Kickstarter guide][kickstarter-sql-guide].

    The example SQL statements used are based on tables in the
    [AdventureWorks][adventureworks] database.
    [AdventureWorks][adventureworks] database. Note that due to the use of the
    existing AdventureWorks schema, some of the guidelines in this document
    are not always followed, especially with regards to naming conventions.
    Those discrepancies will be called out as they appear.

    **NOTE**: This style guide is written for use with [Microsoft SQL
    Server][mssql], but much of it can be applied to any SQL database with some
    @@ -58,8 +60,9 @@ simple modifications.

    ## Quick look

    Before getting into all the rules, here is a quick look at some examples showing
    well formatted SQL that matches the recommendations in this style guide:
    Before getting into all the specifics, here is a quick look at some examples
    showing well formatted, beautiful SQL that matches the recommendations in
    this style guide:

    ```sql
    -- basic select example
    @@ -79,32 +82,25 @@ select p.Name as ProductName

    ```sql
    -- basic insert example
    insert into Sales.Currency (
    CurrencyCode
    ,Name
    ,ModifiedDate
    )
    values (
    'XBT'
    ,'Bitcoin'
    ,getutcdate()
    )
    insert into Sales.Currency (CurrencyCode, Name, ModifiedDate)
    values ('XBT', 'Bitcoin', getutcdate())
    , ('ETH', 'Ethereum', getutcdate())
    ```

    ```sql
    -- basic update example
    update p
    set p.ListPrice = p.ListPrice * 1.05
    , p.ModifiedDate = getutcdate()
    from Production.Product p
    from Production.Product as p
    where p.SellEndDate is null
    and p.SellStartDate is not null
    ```

    ```sql
    -- basic delete example
    delete cc
    from Sales.CreditCard cc
    from Sales.CreditCard as cc
    where cc.ExpYear < '2003'
    and cc.ModifiedDate < dateadd(year, -1, getutcdate())
    ```
    @@ -145,14 +141,19 @@ for modern SQL development. Using lowercase keywords is preferred because:
    * UPPERCASE words are harder to type and
    [harder to read][shoutcase-typeography].
    * SQL syntax is not case-sensitive, and thus lowercase keywords work correctly
    in all variants of SQL, not just SQL Server.
    in all variants of SQL
    * No other modern languages since BASIC use ALLCAPS keywords.
    * Modern editors color code SQL keywords, so there is not a need to distinguish
    keywords by casing.
    * UPPERCASE IS ASSOCIATED WITH SHOUTING WHEN SEEN IN TEXT, AND MAKES SQL FEEL
    MORE LIKE COBOL THAN A MODERN LANGUAGE.
    * If you are in an environment where your keywords are not colored (i.e. as a
    string in another language), using a river for formatting provides a similar
    benefit of highlighting important keywords without resorting to CAPS.
    * UPPERCASE IS ASSOCIATED WITH SHOUTING WHEN SEEN IN TEXT, IS HARD TO READ, AND
    MAKES SQL FEEL MORE LIKE COBOL THAN A MODERN LANGUAGE.

    If the SQL script you are editing already uses SHOUTCASE keywords, match that
    style or change all keywords to lowercase. Favor consistency over mixed casing.
    style or change *all* keywords to lowercase. Favor bending the rules for the
    sake of consistency rather than mixing styles.

    ### Naming guidance

    @@ -171,27 +172,31 @@ style or change all keywords to lowercase. Favor consistency over mixed casing.
    * Naming a table as a plural makes the table easier to speak about. (e.g.
    favor `employees` over `employee`)
    * Do not use object prefixes or Hungarian notation (e.g. `sp_`, `prc_`, `vw_`,
    `tbl`, `t_`, `fn_`, etc).
    `tbl_`, `t_`, `fn_`, etc).
    * Tables with semantic prefixes are okay if they aid understanding the nature
    of a table (e.g. in a Data Warehouse where it is common to use prefixes like
    `Dim` and `Fact`).
    * Never give a table the same name as one of its columns and vice versa.
    * Use a joining word for many-to-many joining tables (cross references) rather
    than concatenating table names (e.g. `Xref`):

    __GOOD__:
    `DriversXrefCars`
    `drivers_xref_cars`

    __BAD__:
    `DriversCars`

    * Tables should always have a primary key. Single column, auto-number (identity)
    surrogate keys are preferable. Natural keys or composite keys can be
    enforced with unique constraints in lieu of making them a primary key.
    Composite keys make for verbose foreign key joins, and `int`/`bigint` primary
    keys offer optimal performance and space for foreign keys when a table gets
    large.
    `drivers_cars`

    * Tables should always have a primary key. A single column, auto-number
    (identity) surrogate key is preferable.
    * Natural keys or composite keys can be enforced with unique constraints in
    lieu of making them a primary key.
    * Composite keys make for verbose and slow foreign key joins. `int`/`bigint`
    primary keys are optimal as foreign keys when a table gets large.
    * Tables should always have `created_at` and `updated_at` metadata fields in
    them to make data movement between systems easier (ETL). Also, consider
    storing deleted records in archival tables, or having a `deleted_at` field for
    soft deletes. Don't forget the needs of data analysts when designing your
    soft deletes.
    * Don't forget the needs of data analysts and ETL developers when designing your
    model.

    #### Columns
    @@ -206,16 +211,18 @@ style or change all keywords to lowercase. Favor consistency over mixed casing.
    the AdventureWorks database, which commonly has columns named `Name` against
    this guide's advice. Remember that an existing convention may be in place that
    is beyond your control._ )
    * Do not use `Desc` as an abbreviation for `Description`. Spell it out, or use
    some other non-keyword.

    #### Aliases

    * Aliases should relate in some way to the object or expression they are aliasing.
    * As a rule of thumb the alias name should be the first letter of each word in
    the object's name or a good abbreviation.
    * As a rule of thumb the alias can be the first letter of each word in the object's
    name or a good abbreviation.
    * If there is already an alias with the same name then append a number.
    * When using a subquery, prefix aliases with an `_` to differentiate them from
    aliases in the outer query.
    * Always include the `as` keyword. It make the query easier to read and is
    * Always include the `as` keyword. It makes the query easier to read and is
    explicit.
    * For computed data (i.e. `sum()` or `avg()`) use the name you would give it were
    it a column defined in the schema.
    @@ -723,7 +730,7 @@ delete prd
    ...
    ```

    __GOOD__:
    __BAD__:

    ```sql
    -- A trailing semicolon is sinister.
    @@ -842,8 +849,6 @@ select per.FirstName, per.LastName
    order by per.LastName, per.FirstName
    ```

    [homepage]: https://gist.github.com/mattmc3/38a85e6a4ca1093816c08d4815fbebfb
    "Modern SQL Style Guide"
    [kickstarter-sql-guide]: https://gist.github.com/fredbenenson/7bb92718e19138c20591#file-kickstarter_sql_style_guide-md
    [sqlstyleguide]: http://www.sqlstyle.guide
    "SQL style guide by Simon Holywell"
  3. mattmc3 renamed this gist Dec 18, 2018. 1 changed file with 12 additions and 12 deletions.
    24 changes: 12 additions & 12 deletions modern_tsql_style_guide.md → modern_sql_style_guide.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,12 +1,12 @@
    ---
    layout: default
    title: Modern T-SQL Style Guide
    title: Modern SQL Style Guide
    revision: 2018-12-18
    version: 0.1.0-beta
    description: A guide to writing clean, clear, and consistent T-SQL.
    description: A guide to writing clean, clear, and consistent SQL.
    ---

    # Modern T-SQL Style Guide
    # Modern SQL Style Guide

    ```sql
    select *
    @@ -17,9 +17,9 @@ select *

    ## Purpose

    These guidelines are designed to make T-SQL statements easy to write, easy to
    These guidelines are designed to make SQL statements easy to write, easy to
    read, easy to maintain, and beautiful to boot. This document is to be used as a
    guide for anyone who would like to write clean and clear T-SQL code.
    guide for anyone who would like to write clean and clear SQL code.

    This guide is opinionated in some areas and relaxed in others. You can use this
    set of guidelines, fork them, or make your own - the key here is that you pick a
    @@ -29,7 +29,7 @@ is a guiding principle to achieve cohesion.
    It is easy to include this guide in Markdown format as a part of a project's
    code base or reference it here for anyone on the project to freely read.

    The original Modern T-SQL style guide [homepage is here][homepage] and is
    The original Modern SQL style guide [homepage is here][homepage] and is
    licensed under a [Creative Commons Attribution-ShareAlike 4.0 International
    License][license].

    @@ -47,7 +47,7 @@ simple modifications.
    ## Principles

    * We take a disciplined and practical approach to writing code.
    * We treat T-SQL like any other source code, which should be checked into
    * We treat SQL like any other source code, which should be checked into
    source control, peer reviewed, and properly maintained.
    * We believe consistency in style is important, and we value craftsmanship, but
    not to the exclusion of other practical concerns.
    @@ -113,8 +113,8 @@ delete cc

    ### General guidance

    * Favor using a ["river"][rivers] for vertical alignment so that a query can be quickly
    and easily be scanned by a new reader.
    * Favor using a ["river"][rivers] for vertical alignment so that a query can be
    quickly and easily be scanned by a new reader.
    * Comments should appear at the top of your query or script, and should explain
    the intent of the query, not the mechanics.
    * Try to comment things that aren't obvious about the query (e.g., why a
    @@ -145,7 +145,7 @@ for modern SQL development. Using lowercase keywords is preferred because:
    * UPPERCASE words are harder to type and
    [harder to read][shoutcase-typeography].
    * SQL syntax is not case-sensitive, and thus lowercase keywords work correctly
    in all variants of SQL, not just T-SQL.
    in all variants of SQL, not just SQL Server.
    * Modern editors color code SQL keywords, so there is not a need to distinguish
    keywords by casing.
    * UPPERCASE IS ASSOCIATED WITH SHOUTING WHEN SEEN IN TEXT, AND MAKES SQL FEEL
    @@ -230,7 +230,7 @@ style or change all keywords to lowercase. Favor consistency over mixed casing.
    * No empty lines in the middle of a single statement.
    * One final newline at the end of a file
    * Use an [.editorConfig file][editor-config] to enforce reasonable whitespace
    rules if your T-SQL editor supports it:
    rules if your SQL editor supports it:

    ```ini
    # .editorConfig is awesome: https://EditorConfig.org
    @@ -843,7 +843,7 @@ select per.FirstName, per.LastName
    ```

    [homepage]: https://gist.github.com/mattmc3/38a85e6a4ca1093816c08d4815fbebfb
    "Modern T-SQL Style Guide"
    "Modern SQL Style Guide"
    [kickstarter-sql-guide]: https://gist.github.com/fredbenenson/7bb92718e19138c20591#file-kickstarter_sql_style_guide-md
    [sqlstyleguide]: http://www.sqlstyle.guide
    "SQL style guide by Simon Holywell"
  4. mattmc3 revised this gist Dec 18, 2018. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion modern_tsql_style_guide.md
    View file
    Original file line number Diff line number Diff line change
    @@ -379,7 +379,7 @@ If using a `select` modifier like `distinct` or `top`, put the first column
    on its own line.

    ```sql
    -- treat the firt column differently when using distinct and top
    -- treat the first column differently when using distinct and top
    select distinct
    p.Color
    , c.Name as CategoryName
  5. mattmc3 revised this gist Dec 18, 2018. 1 changed file with 9 additions and 8 deletions.
    17 changes: 9 additions & 8 deletions modern_tsql_style_guide.md
    View file
    Original file line number Diff line number Diff line change
    @@ -207,17 +207,18 @@ style or change all keywords to lowercase. Favor consistency over mixed casing.
    this guide's advice. Remember that an existing convention may be in place that
    is beyond your control._ )

    #### Aliasing or correlations
    #### Aliases

    * Should relate in some way to the object or expression they are aliasing.
    * As a rule of thumb the correlation name should be the first letter of each
    word in the object's name or a good abbreviation.
    * If there is already a correlation with the same name then append a number.
    * Aliases should relate in some way to the object or expression they are aliasing.
    * As a rule of thumb the alias name should be the first letter of each word in
    the object's name or a good abbreviation.
    * If there is already an alias with the same name then append a number.
    * When using a subquery, prefix aliases with an `_` to differentiate them from
    aliases in the outer query.
    * Always include the `as` keyword—makes it easier to read as it is explicit.
    * For computed data (`sum()` or `avg()`) use the name you would give it were it
    a column defined in the schema.
    * Always include the `as` keyword. It make the query easier to read and is
    explicit.
    * For computed data (i.e. `sum()` or `avg()`) use the name you would give it were
    it a column defined in the schema.

    ### Whitespace

  6. mattmc3 revised this gist Dec 18, 2018. 1 changed file with 8 additions and 1 deletion.
    9 changes: 8 additions & 1 deletion modern_tsql_style_guide.md
    View file
    Original file line number Diff line number Diff line change
    @@ -2,12 +2,19 @@
    layout: default
    title: Modern T-SQL Style Guide
    revision: 2018-12-18
    version: 0.0.5-alpha
    version: 0.1.0-beta
    description: A guide to writing clean, clear, and consistent T-SQL.
    ---

    # Modern T-SQL Style Guide

    ```sql
    select *
    from modern.sql_style_guide guide
    where guide.attributes in ('clean', 'easy', 'sensible')
    and guide.look = 'beautiful'
    ```

    ## Purpose

    These guidelines are designed to make T-SQL statements easy to write, easy to
  7. mattmc3 revised this gist Dec 18, 2018. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions modern_tsql_style_guide.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,7 @@
    ---
    layout: default
    title: Modern T-SQL Style Guide
    revision: 2018-12-18
    version: 0.0.5-alpha
    description: A guide to writing clean, clear, and consistent T-SQL.
    ---
  8. mattmc3 revised this gist Dec 18, 2018. 1 changed file with 22 additions and 11 deletions.
    33 changes: 22 additions & 11 deletions modern_tsql_style_guide.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,7 +1,7 @@
    ---
    layout: default
    title: Modern T-SQL Style Guide
    version: 0.0.4-alpha
    version: 0.0.5-alpha
    description: A guide to writing clean, clear, and consistent T-SQL.
    ---

    @@ -21,8 +21,9 @@ is a guiding principle to achieve cohesion.
    It is easy to include this guide in Markdown format as a part of a project's
    code base or reference it here for anyone on the project to freely read.

    Modern T-SQL style guide is licensed under a
    [Creative Commons Attribution-ShareAlike 4.0 International License][license].
    The original Modern T-SQL style guide [homepage is here][homepage] and is
    licensed under a [Creative Commons Attribution-ShareAlike 4.0 International
    License][license].

    Based on various existing attempts at SQL standards including:
    [http://www.sqlstyle.guide][sqlstyleguide] and
    @@ -300,13 +301,22 @@ team, then a standard 4 space indent can be used in place of a river.
    Major keywords starting a clause should occupying their own line. Major keywords
    are:

    * `select`
    * `into`
    * `from`
    * `where`
    * `group by`
    * `having`
    * `order by`
    * Select statement
    * `select`
    * `into`
    * `from`
    * `where`
    * `group by`
    * `having`
    * `order by`
    * Insert statement additions
    * `insert into`
    * `values`
    * Update statement additions
    * `update`
    * `set`
    * Delete statement additions
    * `delete`

    All other keywords are minor and should appear after the indent and not
    occupy a line to themselves. Other than this section, this guide will stick to
    @@ -823,7 +833,8 @@ select per.FirstName, per.LastName
    order by per.LastName, per.FirstName
    ```

    [mmc]: https://github.com/mattmc3
    [homepage]: https://gist.github.com/mattmc3/38a85e6a4ca1093816c08d4815fbebfb
    "Modern T-SQL Style Guide"
    [kickstarter-sql-guide]: https://gist.github.com/fredbenenson/7bb92718e19138c20591#file-kickstarter_sql_style_guide-md
    [sqlstyleguide]: http://www.sqlstyle.guide
    "SQL style guide by Simon Holywell"
  9. mattmc3 revised this gist Dec 18, 2018. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion modern_tsql_style_guide.md
    View file
    Original file line number Diff line number Diff line change
    @@ -21,7 +21,7 @@ is a guiding principle to achieve cohesion.
    It is easy to include this guide in Markdown format as a part of a project's
    code base or reference it here for anyone on the project to freely read.

    Modern T-SQL style guide created by [Matt McElheny][mmc] is licensed under a
    Modern T-SQL style guide is licensed under a
    [Creative Commons Attribution-ShareAlike 4.0 International License][license].

    Based on various existing attempts at SQL standards including:
  10. mattmc3 revised this gist Dec 18, 2018. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions modern_tsql_style_guide.md
    View file
    Original file line number Diff line number Diff line change
    @@ -133,8 +133,8 @@ Do not SHOUTCASE or "Sentence case" SQL keywords (e.g., prefer `select`, not
    `SELECT` or `Select`). SHOUTCASED SQL is an anachronism, and is not appropriate
    for modern SQL development. Using lowercase keywords is preferred because:

    * UPPERCASE words are harder to type and [harder to read]
    [shoutcase-typeography].
    * UPPERCASE words are harder to type and
    [harder to read][shoutcase-typeography].
    * SQL syntax is not case-sensitive, and thus lowercase keywords work correctly
    in all variants of SQL, not just T-SQL.
    * Modern editors color code SQL keywords, so there is not a need to distinguish
  11. mattmc3 revised this gist Dec 18, 2018. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion modern_tsql_style_guide.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,7 +1,7 @@
    ---
    layout: default
    title: Modern T-SQL Style Guide
    version: 0.0.3-alpha
    version: 0.0.4-alpha
    description: A guide to writing clean, clear, and consistent T-SQL.
    ---

  12. mattmc3 revised this gist Dec 18, 2018. 1 changed file with 6 additions and 6 deletions.
    12 changes: 6 additions & 6 deletions modern_tsql_style_guide.md
    View file
    Original file line number Diff line number Diff line change
    @@ -129,15 +129,15 @@ delete cc

    ### Casing

    Do not SHOUTCASE or "Sentense case" SQL keywords (e.g., prefer `select`, not
    `SELECT`). SHOUTCASED SQL is an anachronism, and is not appropriate for modern
    SQL development. Using lowercase keywords is preferred because:
    Do not SHOUTCASE or "Sentence case" SQL keywords (e.g., prefer `select`, not
    `SELECT` or `Select`). SHOUTCASED SQL is an anachronism, and is not appropriate
    for modern SQL development. Using lowercase keywords is preferred because:

    * UPPERCASE words are harder to type and [harder to read]
    [shoutcase-typeography].
    * SQL syntax is not case-sensitive, and thus lowecase keywords work correctly
    * SQL syntax is not case-sensitive, and thus lowercase keywords work correctly
    in all variants of SQL, not just T-SQL.
    * Moden editors color code SQL keywords, so there is not a need to distinguish
    * Modern editors color code SQL keywords, so there is not a need to distinguish
    keywords by casing.
    * UPPERCASE IS ASSOCIATED WITH SHOUTING WHEN SEEN IN TEXT, AND MAKES SQL FEEL
    MORE LIKE COBOL THAN A MODERN LANGUAGE.
    @@ -196,7 +196,7 @@ style or change all keywords to lowercase. Favor consistency over mixed casing.
    tables with similarly named columns. (**NOTE:** _This guide uses
    the AdventureWorks database, which commonly has columns named `Name` against
    this guide's advice. Remember that an existing convention may be in place that
    is beyond your control.__ )
    is beyond your control._ )

    #### Aliasing or correlations

  13. mattmc3 revised this gist Dec 18, 2018. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions modern_tsql_style_guide.md
    View file
    Original file line number Diff line number Diff line change
    @@ -28,8 +28,8 @@ Based on various existing attempts at SQL standards including:
    [http://www.sqlstyle.guide][sqlstyleguide] and
    [Kickstarter guide][kickstarter-sql-guide].

    The example SQL statements used are based on tables in the [AdventureWorks]
    [adventureworks] database.
    The example SQL statements used are based on tables in the
    [AdventureWorks][adventureworks] database.

    **NOTE**: This style guide is written for use with [Microsoft SQL
    Server][mssql], but much of it can be applied to any SQL database with some
  14. mattmc3 revised this gist Dec 18, 2018. 1 changed file with 484 additions and 312 deletions.
    796 changes: 484 additions & 312 deletions modern_tsql_style_guide.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,34 +1,35 @@
    ---
    layout: default
    title: Modern T-SQL Style Guide
    version: 0.0.2-alpha
    version: 0.0.3-alpha
    description: A guide to writing clean, clear, and consistent T-SQL.
    ---

    # Modern T-SQL Style Guide

    ## Purpose

    You can use this set of guidelines, fork them, or make your own - the key here
    is that you pick a style and stick to it. The odds of making everyone happy
    are low, so compromise is a guiding principle to achieve cohesion.

    These guidelines are designed to make T-SQL statements easy to write, easy to
    read, and easy to maintain. This guide is opinionated in some areas and relaxed
    in others.
    read, easy to maintain, and beautiful to boot. This document is to be used as a
    guide for anyone who would like to write clean and clear T-SQL code.

    This guide is opinionated in some areas and relaxed in others. You can use this
    set of guidelines, fork them, or make your own - the key here is that you pick a
    style and stick to it. The odds of making everyone happy are low, so compromise
    is a guiding principle to achieve cohesion.

    It is easy to include this guide in Markdown format as a part of a project's
    code base or reference it here for anyone on the project to freely read.

    Modern T-SQL style guide created by [Matt McElheny][mmc] is licensed under a
    [Creative Commons Attribution-ShareAlike 4.0 International License][licence].
    [Creative Commons Attribution-ShareAlike 4.0 International License][license].

    Based on various SQL standards including
    Based on various existing attempts at SQL standards including:
    [http://www.sqlstyle.guide][sqlstyleguide] and
    [Kickstarter guide][kickstarter-sql-guide].

    This document is to be used as a guide for anyone who would like to write clean
    and clear T-SQL code that is meant to be shared.
    The example SQL statements used are based on tables in the [AdventureWorks]
    [adventureworks] database.

    **NOTE**: This style guide is written for use with [Microsoft SQL
    Server][mssql], but much of it can be applied to any SQL database with some
    @@ -43,6 +44,8 @@ simple modifications.
    not to the exclusion of other practical concerns.
    * We demonstrate intent explicitly in code, via clear structure and comments
    where needed.
    * We adhere to a consistent style for handwritten SQL so that our code can
    thrive in an environment with many authors, editors, and readers.

    ## Quick look

    @@ -51,84 +54,106 @@ well formatted SQL that matches the recommendations in this style guide:

    ```sql
    -- basic select example
    select u.user_name as employee_user_name
    , u.created as created_date
    , d.[name] as department_name
    from dbo.users as u
    join dbo.departments as d on u.department_id = d.id
    where u.created >= dateadd(year, -1, getutcdate())
    order by created_date desc
    select p.Name as ProductName
    , p.ProductNumber
    , pm.Name as ProductModelName
    , p.Color
    , p.ListPrice
    from Production.Product as p
    join Production.ProductModel as pm
    on p.ProductModelID = pm.ProductModelID
    where p.Color in ('Blue', 'Red')
    and p.ListPrice < 800.00
    and pm.Name like '%frame%'
    order by p.Name
    ```

    ```sql
    -- basic insert example
    insert into dbo.categories (
    category_code
    ,category_name
    ,created_at
    insert into Sales.Currency (
    CurrencyCode
    ,Name
    ,ModifiedDate
    )
    values (
    'MOV'
    ,'Movies'
    'XBT'
    ,'Bitcoin'
    ,getutcdate()
    )
    ```

    ```sql
    -- basic update example
    update e
    set e.salary = e.salary * 1.04
    , updated_at = getutcdate()
    from dbo.employees as e
    where e.department_id in (1, 2, 3)
    update p
    set p.ListPrice = p.ListPrice * 1.05
    , p.ModifiedDate = getutcdate()
    from Production.Product p
    where p.SellEndDate is null
    and p.SellStartDate is not null
    ```

    ```sql
    -- basic delete example
    delete g
    from dbo.gifts as g
    where g.is_purchased = 1
    and g.updated_at <= dateadd(month, -6, getutcdate())
    and g.return_date is null
    delete cc
    from Sales.CreditCard cc
    where cc.ExpYear < '2003'
    and cc.ModifiedDate < dateadd(year, -1, getutcdate())
    ```

    ## Rules

    ### General guidance

    * Do not SHOUTCASE SQL keywords (e.g., prefer `select`, not `select`)
    * Favor using a "river" for vertical alignment so that a query can be quickly
    * Favor using a ["river"][rivers] for vertical alignment so that a query can be quickly
    and easily be scanned by a new reader.
    * Comments should appear at the top of your query, and should explain the intent
    of the query, not the mechanics.
    * Comments should appear at the top of your query or script, and should explain
    the intent of the query, not the mechanics.
    * Try to comment things that aren't obvious about the query (e.g., why a
    particular filter is necessary, why an optimization trick was needed, etc.)
    * Favor being descriptive over terseness:

    __GOOD__:
    `select usr.user_name as customer_user_name`
    `select emp.LoginID as EmployeeUserName`

    __BAD__:
    `select usr.user_name as custnm`
    `select emp.LoginID as EmpUsrNm`

    * Follow any existing style in the script before applying this style guide.
    The SQL script should have one clear style, and these rules should not be
    applied to existing scripts unless the whole script is being changed to
    adhere to this style guide.
    adhere to the same style.
    * Favor storing `datetime` and `datetime2` in UTC unless embedding timezone
    information (`datetimeoffset`) so that times are clear and convertible.
    Use [ISO-8601][iso-8601] compliant time and date information
    (`YYYY-MM-DD HH:MM:SS.SSSSS`) when referring to date/time data.

    ### Casing

    Do not SHOUTCASE or "Sentense case" SQL keywords (e.g., prefer `select`, not
    `SELECT`). SHOUTCASED SQL is an anachronism, and is not appropriate for modern
    SQL development. Using lowercase keywords is preferred because:

    * UPPERCASE words are harder to type and [harder to read]
    [shoutcase-typeography].
    * SQL syntax is not case-sensitive, and thus lowecase keywords work correctly
    in all variants of SQL, not just T-SQL.
    * Moden editors color code SQL keywords, so there is not a need to distinguish
    keywords by casing.
    * UPPERCASE IS ASSOCIATED WITH SHOUTING WHEN SEEN IN TEXT, AND MAKES SQL FEEL
    MORE LIKE COBOL THAN A MODERN LANGUAGE.

    If the SQL script you are editing already uses SHOUTCASE keywords, match that
    style or change all keywords to lowercase. Favor consistency over mixed casing.

    ### Naming guidance

    * Names should be `underscore_separated` or `PascalCase` but do not mix styles.

    __GOOD__:
    `select count(*) as the_tally, sum(*) as the_total`
    `select count(*) as the_tally, sum(*) as the_total ...`

    __BAD__:
    `select count(*) as TheTally, sum(*) as theTotal`
    `select count(*) as The_Tally, sum(*) as theTotal ...`

    #### Tables

    @@ -139,34 +164,48 @@ delete g
    * Do not use object prefixes or Hungarian notation (e.g. `sp_`, `prc_`, `vw_`,
    `tbl`, `t_`, `fn_`, etc).
    * Never give a table the same name as one of its columns and vice versa.
    * Use `_xref_` for many-to-many tables rather than concatenating table names:
    * Use a joining word for many-to-many joining tables (cross references) rather
    than concatenating table names (e.g. `Xref`):

    __GOOD__:
    `drivers_xref_cars`
    `DriversXrefCars`

    __BAD__:
    `drivers_cars`
    `DriversCars`

    * Tables should always have a primary key. Surrogate, auto-number identity
    primary keys are preferable in most circumstances. Natural keys can be
    * Tables should always have a primary key. Single column, auto-number (identity)
    surrogate keys are preferable. Natural keys or composite keys can be
    enforced with unique constraints in lieu of making them a primary key.
    Composite keys make for verbose foreign key joins, and `int`/`bigint` primary
    keys offer optimal performance and space for foreign keys when a table gets
    large.
    * Tables should always have `created_at` and `updated_at` metadata fields in
    them to make data movement between systems easier (ETL). Also, consider
    storing deleted records in archival tables, or having a `deleted_at` field for
    soft deletes. Don't forget the needs of data analysts when designing your
    model.

    #### Columns

    * Do not use [reserved words][sql-server-keywords] for column names if possible.
    * Prefer not simply using `id` as the primary identifier for the table if
    possible.
    * Prefer not simply using `id` as the name of the primary identifier for the
    table if possible.
    * Do not add a column with the same name as its table and vice versa.
    * Avoid common words like `name`, `description`, etc. Prefer a descriptive
    * Avoid common words like `Name`, `Description`, etc. Prefer a descriptive
    prefix for those words so that they don't require aliases when joined to other
    tables with similarly named columns.
    tables with similarly named columns. (**NOTE:** _This guide uses
    the AdventureWorks database, which commonly has columns named `Name` against
    this guide's advice. Remember that an existing convention may be in place that
    is beyond your control.__ )

    #### Aliasing or correlations

    * Should relate in some way to the object or expression they are aliasing.
    * as a rule of thumb the correlation name should be the first letter of each
    word in the object's name.
    * As a rule of thumb the correlation name should be the first letter of each
    word in the object's name or a good abbreviation.
    * If there is already a correlation with the same name then append a number.
    * When using a subquery, prefix aliases with an `_` to differentiate them from
    aliases in the outer query.
    * Always include the `as` keyword—makes it easier to read as it is explicit.
    * For computed data (`sum()` or `avg()`) use the name you would give it were it
    a column defined in the schema.
    @@ -175,13 +214,13 @@ delete g

    * No tabs. Use spaces for indents.
    * Configure your editor to 4 spaces per indent, but prefer your SQL to indent
    to the "river" and not to a set indent increment.
    to the "river", and not to a set indent increment.
    * No trailing whitespace.
    * No more than two lines between statements.
    * No more than two blank lines between statements.
    * No empty lines in the middle of a single statement.
    * One final newline at the end of a file
    * Use an [.editorConfig file][editor-config] to enforce reasonable whitespace rules if your T-SQL
    editor supports it:
    * Use an [.editorConfig file][editor-config] to enforce reasonable whitespace
    rules if your T-SQL editor supports it:

    ```ini
    # .editorConfig is awesome: https://EditorConfig.org
    @@ -198,158 +237,214 @@ insert_final_newline = true

    ### River formatting

    Spaces should be used to line up the code so that the root keywords all end on
    the same character boundary. This forms a "river" down the middle making it easy
    for the readers eye to scan over the code and separate the keywords from the
    Spaces may be used to line up the code so that the root keywords all end on the
    same character boundary. This forms a "river" down the middle making it easy for
    the reader's eye to scan over the code and separate the keywords from the
    implementation detail. Rivers are [bad in typography][rivers], but helpful here.
    [Celko's book][celko] describes using a river to vertically align your query.
    Right align keywords to the river.
    Right align keywords to the river if you chose to use one. The `on` clause in
    the `from` may have its own river to help align information vertically.
    Subqueries should create their own river as well.

    ```sql
    -- a river in the 7th column helps vertical readability
    select emp.id as employee_id
    , dpt.name as department
    , dpt.location
    , emp.first_name as employee_first_name
    , emp.last_name as employee_last_name
    , mgr.first_name as manager_first_name
    , mgr.last_name as manager_last_name
    from dbo.employees as emp
    join dbo.departments as dpt on emp.department_id = dpt.id
    left join dbo.employees as mgr on emp.manager_id = mgr.id
    where dpt.name in ('IT', 'DBA', 'Analytics')
    and (emp.title like '%DBA%' or emp.title like '%Database%')
    order by dpt.name
    , emp.last_name
    , emp.first_name
    select prdct.Name as ProductName
    , prdct.ListPrice
    , prdct.Color
    , cat.Name as CategoryName
    , subcat.Name as SubcategoryName
    from Production.Product as prdct
    left join Production.ProductSubcategory as subcat
    on prdct.ProductSubcategoryID = subcat.ProductSubcategoryID
    left join Production.ProductCategory as cat
    on subcat.ProductCategoryID = cat.ProductCategoryID
    where prdct.ListPrice <= 1000.00
    and prdct.ProductID not in (
    select _pd.ProductID
    from Production.ProductDocument _pd
    where _pd.ModifiedDate < dateadd(year, -1, getutcdate())
    )
    and prdct.Color in ('Black', 'Red', 'Silver')
    order by prdct.ListPrice desc, prdct.Name
    ```

    ```sql
    -- alternately, a river in the a different column is fine if that is preferred
    -- due to longer keywords, but know that indenting can feel off if the `select`
    -- is not in the first column for the query
    select emp.id as employee_id
    , dpt.name as department
    , dpt.location
    , emp.first_name as employee_first_name
    , emp.last_name as employee_last_name
    , mgr.first_name as manager_first_name
    , mgr.last_name as manager_last_name
    from dbo.employees as emp
    join dbo.departments as dpt on emp.department_id = dpt.id
    left join dbo.employees as mgr on emp.manager_id = mgr.id
    where dpt.name in ('IT', 'DBA', 'Analytics')
    and (emp.title like '%DBA%' or emp.title like '%Database%')
    order by dpt.name
    , emp.last_name
    , emp.first_name
    ```

    ```sql
    -- Using a river can be tedious, so if this alignment is not preferred by your
    -- team, then standard 4 space indenting should be used in place of a river
    -- with the major keywords occupying their own line. (i.e. `select`, `from`,
    -- `where`, `group by`, `having`, `order by`):
    -- due to longer keywords, but know that indenting can feel "off" if the
    -- `select` is not in the first column for the query
    select prdct.Name as ProductName
    , prdct.ListPrice
    , prdct.Color
    , cat.Name as CategoryName
    , subcat.Name as SubcategoryName
    from Production.Product as prdct
    left join Production.ProductSubcategory as subcat
    on prdct.ProductSubcategoryID = subcat.ProductSubcategoryID
    left join Production.ProductCategory as cat
    on subcat.ProductCategoryID = cat.ProductCategoryID
    where prdct.ListPrice <= 1000.00
    and prdct.ProductID not in (
    select _pd.ProductID
    from Production.ProductDocument _pd
    where _pd.ModifiedDate < dateadd(year, -1, getutcdate())
    )
    and prdct.Color in ('Black', 'Red', 'Silver')
    order by prdct.ListPrice desc, prdct.Name
    ```

    ### Indent formatting

    Using a river can be tedious, so if this alignment is not preferred by your
    team, then a standard 4 space indent can be used in place of a river.

    Major keywords starting a clause should occupying their own line. Major keywords
    are:

    * `select`
    * `into`
    * `from`
    * `where`
    * `group by`
    * `having`
    * `order by`

    All other keywords are minor and should appear after the indent and not
    occupy a line to themselves. Other than this section, this guide will stick to
    showing "river" formatting examples.

    ```sql
    -- Editors tend to handle indenting style better than river alignment. River
    -- formatting has advantages over indent formatting, but this style is
    -- acceptable.
    select
    emp.id as employee_id
    ,dpt.name as department
    ,dpt.location
    ,emp.first_name as employee_first_name
    ,emp.last_name as employee_last_name
    ,mgr.first_name as manager_first_name
    ,mgr.last_name as manager_last_name
    prdct.Name as ProductName
    ,prdct.ListPrice
    ,prdct.Color
    ,cat.Name as CategoryName
    ,subcat.Name as SubcategoryName
    from
    dbo.employees as emp
    join dbo.departments as dpt on emp.department_id = dpt.id
    left join dbo.employees as mgr on emp.manager_id = mgr.id
    Production.Product as prdct
    left join Production.ProductSubcategory as subcat
    on prdct.ProductSubcategoryID = subcat.ProductSubcategoryID
    left join Production.ProductCategory as cat
    on subcat.ProductCategoryID = cat.ProductCategoryID
    where
    dpt.name in ('IT', 'DBA', 'Analytics')
    and (emp.title like '%DBA%' or emp.title like '%Database%')
    prdct.ListPrice <= 1000.00
    and prdct.Color in ('Black', 'Red', 'Silver')
    order by
    dpt.name
    ,emp.last_name
    ,emp.first_name
    prdct.ListPrice desc, prdct.Name
    ```

    ### `select` clause

    select the first column on the same line, and align all additional columns to
    the first column on their own line:
    Select the first column on the same line, and align all subsequent columns
    after the first get their own line.

    ```sql
    select projects.name
    , users.email
    , projects.country
    , count(backings.id) as backings_count
    select prdct.Color
    , cat.Name as CategoryName
    , count(*) as ProductCount
    from ...
    ```

    If three or fewer columns are selected, have short names, and don't need
    aliased, you may chose to have them occupy the same line for brevity.

    ```sql
    -- shortcut for small columns
    select p.Color, c.Name, p.ListPrice
    from ...
    ```

    If using a `select` modifier like `distinct` or `top`, put the first column
    on its own line.

    ```sql
    -- treat the firt column differently when using distinct and top
    select distinct
    p.Color
    , c.Name as CategoryName
    from ...
    ```

    Use commas as a prefix as opposed to a suffix. This is preferred because:

    * It makes it easy to add new columns to the end of the column list
    * It prevents unintentional aliasing bugs
    * It makes commenting out columns much easier
    * Column lists are more likely to change at the end than at the beginning
    * It makes it easy to add new columns to the end of the column list, which is
    more common than at the beginning
    * It prevents unintentional aliasing bugs (missing comma)
    * It makes commenting out columns at the end easier
    * When statements take multiple lines like windowing functions and `case`
    statements, the prefix comma makes it clear when a new column starts
    * It does not adversely affect readability

    The comma should border the "river".
    The comma should border the "river" on the keyword side.

    __GOOD__:

    ```sql
    select user_name as customer_user_name
    , created_at
    , is_deleted
    select Name
    , ListPrice
    , Color
    , CategoryName
    ...
    ```

    __BAD__:

    ```sql
    -- whoops! forgot a trailing comma because it's hard to see, making an
    -- accidental alias
    select usr.user_name as customer_user_name,
    created_at
    is_deleted
    -- accidental alias of `ListPrice Color`
    select Name,
    ListPrice
    Color,
    CategoryName
    ...
    ```

    Always rename aggregates, `case` derived columns, and function-wrapped columns:
    Always use `as` to rename columns. `as` statements can be used for additional
    vertical alignment but don't have to be:

    __GOOD__:

    ```sql
    select product_name
    , sum(amount) as sum_amount
    select prdct.Color as ProductColor
    , cat.Name as CategoryName
    , count(*) as ProductCount
    from ...
    ...
    ```

    Always use table aliases for all columns when querying from more than one table.
    Single character aliases are fine for a few tables, but are less likely to be
    clear as a query grows:
    __BAD__:

    ```sql
    select prj.name as project_name
    , count(bck.id) as backings_count
    from dbo.backings as bck
    join dbo.projects as prj
    on ...
    select prdct.Color ProductColor
    , cat.Name CategoryName
    , count(*) ProductCount
    from ...
    ...
    ```

    Always use `as` to rename columns. `as` statements can be used for additional
    vertical alignment but don't have to be:

    __GOOD__:
    Always rename aggregates, derived columns (e.g. `case` statements), and
    function-wrapped columns:

    ```sql
    select projects.name as project_name
    , count(backings.id) as backings_count
    ...
    select ProductName
    , sum(UnitPrice * OrderQty) as TotalCost
    , getutcdate() as NowUTC
    from ...
    ```

    __BAD__:
    Always use table alias prefixes for all columns when querying from more than one
    table. Single character aliases are fine for a few tables, but are less likely
    to be clear as a query grows:

    ```sql
    select prj.name project_name
    , count(bck.id) backings_count
    ...
    select prdct.Color
    , subcat.Name as SubcategoryName
    , count(*) as ItemCount
    from Production.Product as prdct
    left join Production.ProductSubcategory as subcat
    on ...
    ```

    Do not bracket-escape table or column names unless the names contain keyword
    @@ -358,40 +453,46 @@ collisions or would cause a syntax error without properly qualifying them.
    __GOOD__:

    ```sql
    -- name and state are keywords
    select [name]
    , street1
    , city
    , [state]
    , zip
    from dbo.people
    -- owner and status are keywords
    select Title
    , [Owner]
    , [Status]
    from Production.Document
    ```

    __BAD__:

    ```sql
    -- the brackets are messy and unnecessary
    select [name]
    , [street1]
    , [city]
    , [state]
    , [zip]
    from [dbo].[people]
    -- extra brackets are messy and unnecessary
    select [Title]
    , [Owner]
    , [Status]
    from [Production].[Document]
    ```

    #### Windowing functions

    Long Window functions should be split across multiple lines: one for each
    clause, aligned to the `partition` keyword. Partition keys can share the same
    line, or be split. Ascending order is an intuitive default and thus using an
    explicit `asc` is not necessary whereas `desc` is. All window functions should
    be aliased.
    clause, aligned with a river. Partition keys can share the same line, or be
    split. Ascending order is an intuitive default and thus using an explicit `asc`
    is not necessary whereas `desc` is. All window functions should be aliased.

    ```sql
    row_number() over (partition by prj.category_id, prj.year
    order by pledged desc, last_name, first_name
    ) as sequence_number
    select p.ProductID
    , p.Name as ProductName
    , p.ProductNumber
    , p.ProductLine
    , row_number() over (partition by p.ProductLine
    , left(p.ProductNumber, 2)
    order by right(p.ProductNumber, 4) desc) as SequenceNum
    , p.Color
    from Production.Product p
    order by p.ProductLine
    , left(p.ProductNumber, 2)
    , SequenceNum
    ```

    ### `case` statements
    #### `case` statements

    `case` statements aren't always easy to format but try to align `when`, `then`,
    and `else` together inside `case` and `end`.
    @@ -400,36 +501,38 @@ and `else` together inside `case` and `end`.
    preferable.

    ```sql
    select prj.name as project_name
    , prj.created_at
    , case when prj.category in ('stuff', 'and', 'things')
    then 'Amazing!'
    else 'Average'
    end as opinion
    from ...
    select dep.Name as DepartmentName
    , case when dep.Name in ('Engineering', 'Tool Design', 'Information Services')
    then 'Information Technology'
    else dep.GroupName
    end as NewGroupName
    from HumanResources.Department as dep
    order by NewGroupName, DepartmentName
    ```

    ### `from` and `join` clause
    ### `from` clause

    Only one table should be in the `from`. Never use comma separated `from`-joins:
    Only one table should be in the `from` part. Never use comma separated
    `from`-joins:

    __GOOD__:

    ```sql
    select projects.name as project_name
    , count(backings.id) as backings_count
    from dbo.projects as projects
    join dbo.backings as backings on backings.project_id = projects.id
    select cust.AccountNumber
    , sto.Name as StoreName
    from Sales.Customer as cust
    join Sales.Store as sto
    on cust.StoreID = sto.BusinessEntityID
    ...
    ```

    __BAD__:

    ```sql
    select projects.name as project_name
    , count(backings.id) as backings_count
    from dbo.projects as projects, dbo.backings as backings
    where backings.project_id = projects.id
    select cust.AccountNumber
    , sto.Name as StoreName
    from Sales.Customer as cust, Sales.Store as sto
    where cust.StoreID = sto.BusinessEntityID
    ...
    ```

    @@ -440,238 +543,307 @@ query, and the full table list is easier to scan without excessive staggering:
    __GOOD__:

    ```sql
    select projects.name as project_name,
    , count(backings.id) as backings_count
    from dbo.projects as projects
    join dbo.backings as backings on ...
    join ...
    left join dbo.backer_rewards as backer_rewards on ...
    left join ...
    -- this is easier to format and read
    select *
    from HumanResources.Employee as emp
    join Person.Person as per
    on emp.BusinessEntityID = per.BusinessEntityID
    left join HumanResources.EmployeeDepartmentHistory as edh
    on emp.BusinessEntityID = edh.BusinessEntityID
    left join HumanResources.Department as dep
    on edh.DepartmentID = dep.DepartmentID
    ```

    __BAD__:

    ```sql
    select projects.name as project_name,
    , count(backings.id) as backings_count
    from dbo.projects as projects
    inner join dbo.backings as backings on ...
    inner join ...
    left outer join dbo.backer_rewards as backer_rewards on ...
    left outer join ...
    -- verbosity for the sake of verbosity is not helpful
    -- `join` by itself always means `inner join`
    -- `outer` is an unnecessary optional keyword
    select *
    from HumanResources.Employee as emp
    inner join Person.Person as per
    on emp.BusinessEntityID = per.BusinessEntityID
    left outer join HumanResources.EmployeeDepartmentHistory as edh
    on emp.BusinessEntityID = edh.BusinessEntityID
    left outer join HumanResources.Department as dep
    on edh.DepartmentID = dep.DepartmentID
    ```

    The `on` keyword and condition goes on the `inner join` line:
    The `on` keyword and condition can go on its own line, but is easier to scan if
    it lines up on the `join` line. This is an acceptable style alternative:

    ```sql
    select projects.name as project_name
    , count(backings.id) as backings_count
    from dbo.projects as projects
    join dbo.backings as backings on projects.id = backings.project_id
    -- this is an easier format to scan visually, but comes at the cost of longer
    -- lines of code.
    select *
    from HumanResources.Employee as emp
    join Person.Person as per on emp.BusinessEntityID = per.BusinessEntityID
    left join HumanResources.EmployeeDepartmentHistory as edh on emp.BusinessEntityID = edh.BusinessEntityID
    left join HumanResources.Department as dep on edh.DepartmentID = dep.DepartmentID
    ...
    ```

    Additional filters in the `join` go on new indented lines. Line up using the
    `on` keyword as a second river:
    `on` keyword:

    __GOOD__:

    ```sql
    select projects.name as project_name
    , count(backings.id) as backings_count
    from dbo.projects as projects
    join dbo.backings as backings on projects.id = backings.project_id
    and backings.project_country <> 'US'
    ...
    select emp.JobTitle
    from HumanResources.Employee as emp
    left join HumanResources.EmployeeDepartmentHistory as edh
    on emp.BusinessEntityID = edh.BusinessEntityID
    left join HumanResources.Department as dep
    on edh.DepartmentID = dep.DepartmentID
    and dep.Name <> dep.GroupName -- multi-conditions start a new line
    where dep.DepartmentID is null
    ```

    __BAD__:

    ```sql
    select emp.JobTitle
    from HumanResources.Employee as emp
    left join HumanResources.EmployeeDepartmentHistory as edh
    on emp.BusinessEntityID = edh.BusinessEntityID
    left join HumanResources.Department as dep
    on edh.DepartmentID = dep.DepartmentID and dep.Name <> dep.GroupName -- needs a new line
    where dep.DepartmentID is null
    ```

    Begin with `inner join`s and then list `left join`s, order them semantically,
    and do not intermingle `left join`s with `inner join`s unless necessary:
    and do not intermingle `left join`s with `inner join`s unless necessary. Order
    the `on` clause with joining aliases referencing tables top-to-bottom:

    __GOOD__:

    ```sql
    join dbo.backings as backings on ...
    join dbo.users as users on ...
    join dbo.locations as locations on ...
    left join dbo.backer_rewards as backer_rewards on ...
    left join ...
    select *
    from Production.Product as prd
    join Production.ProductModel as prm
    on prd.ProductModelID = prm.ProductModelID
    left join Production.ProductSubcategory as psc
    on prd.ProductSubcategoryID = psc.ProductSubcategoryID
    left join Production.ProductDocument as doc
    on prd.ProductID = doc.ProductID
    ```

    __BAD__:

    ```sql
    left join dbo.backer_rewards as backer_rewards on backings
    join dbo.users as users on ...
    left join ...
    join dbo.locations as locations on ...
    select *
    from Production.Product as prd
    left join Production.ProductSubcategory as psc
    on psc.ProductSubcategoryID = prd.ProductSubcategoryID -- backwards
    join Production.ProductModel as prm -- intermingled
    on prm.ProductModelID = prd.ProductModelID -- backwards
    left join Production.ProductDocument as doc
    on prd.ProductID = doc.ProductID
    ```

    Avoid `right joins` as they are usually better written with a `left join`

    __BAD__:
    __GOOD__:

    ```sql
    from dbo.backer_rewards as backer_rewards
    right join dbo.users as users on ...
    select *
    from Production.Product as prd
    left join Production.ProductSubcategory as psc
    on ...
    ```

    __GOOD__:
    __BAD__:

    ```sql
    from dbo.users as users
    left join dbo.backer_rewards as backer_rewards on ...
    select *
    from Production.ProductSubcategory as psc
    right join Production.Product as prd
    on ...
    ```

    ### `where` clause

    Multiple `where` clauses should go on different lines and align to the river:

    ```sql
    select name
    , goal
    from dbo.projects as projects
    where country = 'US'
    and deadline >= '2015-01-01'
    select *
    from Production.Product prd
    where prd.Weight > 2.5
    and prd.ListPrice < 1500.00
    and Color in ('Blue', 'Black', 'Red')
    and SellStartDate >= '2006-01-01'
    ...
    ```

    When mixing `and` and `or` statements, do not rely on order of operations and
    instead always use parenthesis to make the intent clear:

    ```sql
    select name
    , goal
    from dbo.projects as projects
    where (country = 'US'
    and deadline >= '2015-01-01')
    or include = 1
    ...
    select *
    from Production.Product prd
    where (prd.Weight > 10.0
    and Color in ('Red', 'Silver'))
    or Color is null
    ```

    Always put a semicolon on its own line when using them. This prevents common
    errors like adding conditions to a `where` clause and neglecting to move the
    trailing semicolon:

    __BAD__:
    __GOOD__:

    ```sql
    -- messing with a where clause, it's easy to miss a trailing semicolon
    delete u
    from dbo.users u
    where created_at < dateadd(year, -1, getutcdate());
    and user_name like '%test_user%' -- whoops, missed that trailing semicolon!
    -- The prefix semicolon is clear and easy to spot when adding to a `where`
    delete prd
    from Production.Product prd
    where prd.ListPrice = 0
    and weight is null
    and size is null
    ;
    ...
    ```

    __GOOD__:

    ```sql
    -- semicolons that start a line are easy to spot
    delete u
    from dbo.users u
    where created_at < dateadd(year, -1, getutcdate())
    and user_name like '%test_user%'
    ; -- < if we add a condition, we'll see this semicolon
    -- A trailing semicolon is sinister.
    -- We added some where conditions and missed it.
    -- This is a destructive bug.
    delete prd
    from Production.Product prd
    where prd.ListPrice = 0; -- dangerous
    and weight is null -- syntax error here, but the bad delete is valid
    and size is null
    ...
    ```

    ### `group by` clause

    Maintain the same column order as the `select` clause in the `group by`:

    __GOOD__:

    ```sql
    select poh.EmployeeID
    , poh.VendorID
    , count(*) as OrderCount
    , avg(poh.SubTotal) as AvgSubTotal
    from Purchasing.PurchaseOrderHeader as poh
    group by poh.EmployeeID
    , poh.VendorID
    ```

    __BAD__:

    ```sql
    -- messing with the 'group by' order makes it hard to scan for accuracy
    select p.project_name
    , p.country
    , sum(p.pledged) as total_pledged
    from dbo.projects as p
    group by p.country -- out of order
    , p.project_name
    select poh.EmployeeID
    , poh.VendorID
    , count(*) as OrderCount
    , avg(poh.SubTotal) as AvgSubTotal
    from Purchasing.PurchaseOrderHeader as poh
    group by poh.VendorID -- out of order
    , poh.EmployeeID
    ```

    __GOOD__:
    ### `having` clause

    A `having` clause is just a `where` clause for aggregate functions. The same
    rules for `where` clauses apply to `having`.

    __Example__:

    ```sql
    select p.project_name
    , p.country
    , sum(p.pledged) as total_pledged
    from dbo.projects as p
    group by p.project_name
    , p.country
    select poh.EmployeeID
    , poh.VendorID
    , count(*) as OrderCount
    , avg(poh.SubTotal) as AvgSubTotal
    from Purchasing.PurchaseOrderHeader as poh
    group by poh.EmployeeID
    , poh.VendorID
    having count(*) > 1
    and avg(poh.SubTotal) > 3000.00
    ```

    ### `order by` clause

    Do not use the superfluous `asc` in `order by` statements:

    __BAD__:
    __GOOD__:

    ```sql
    -- asc is clutter - it's never ambiguous when you wanted to sort ascending
    select p.project_name
    , p.country
    from dbo.projects as p
    order by p.country asc
    , p.project_name asc
    -- asc is implied and obvious
    select per.LastName
    , per.FirstName
    from Person.Person per
    order by per.LastName
    , per.FirstName
    ```

    __GOOD__:
    __BAD__:

    ```sql
    -- asc is clutter - it's never ambiguous when you wanted to sort ascending
    select p.project_name
    , p.country
    from dbo.projects as p
    order by p.country
    , p.project_name
    select per.LastName
    , per.FirstName
    from Person.Person per
    order by per.LastName asc -- useless asc
    , per.FirstName asc
    ```

    Ordering by column number is okay, but not preferred:

    ```sql
    -- This is okay, but not great. It's better when running ad-hoc queries or
    -- queries with calculated columns
    select p.project_name
    , case when p.category in ('Science', 'Technology', 'Engineering')
    then 'STEM'
    else 'Other'
    end as category_group
    , p.country
    from dbo.projects as p
    order by 2, 1
    -- This is okay, but not great.
    select per.FirstName + ' ' + per.LastName as FullName
    , per.LastName + ', ' + per.FirstName as LastFirst
    from Person.Person per
    order by 2
    ```

    The `by` keyword can sit on the other side of a 7th column river, but align
    the order by columns:

    ```sql
    select per.FirstName
    , per.LastName
    from Person.Person per
    order by per.LastName
    , per.FirstName
    ```

    If three or fewer columns are in the `order by` and have short names you may
    chose to have them occupy the same line for brevity.

    ```sql
    -- shortcut for small columns
    select per.FirstName, per.LastName
    from Person.Person per
    order by per.LastName, per.FirstName
    ```

    [mmc]: https://github.com/mattmc3
    [kickstarter-sql-guide]: https://gist.github.com/fredbenenson/7bb92718e19138c20591#file-kickstarter_sql_style_guide-md
    [sqlstyleguide]: http://www.sqlstyle.guide
    "SQL style guide by Simon Holywell"
    [adventureworks]: https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure
    "AdventureWorks sample database"
    [shoutcase-typeography]: https://practicaltypography.com/all-caps.html
    [editor-config]: https://EditorConfig.org
    [mssql]: https://www.microsoft.com/en-us/sql-server/default.aspx
    "Microsoft SQL Server"
    [simon]: https://www.simonholywell.com/?utm_source=sqlstyle.guide&utm_medium=link&utm_campaign=md-document
    "SimonHolywell.com"
    [issue]: https://github.com/treffynnon/sqlstyle.guide/issues
    "SQL style guide issues on GitHub"
    [fork]: https://github.com/treffynnon/sqlstyle.guide/fork
    "Fork SQL style guide on GitHub"
    [pull]: https://github.com/treffynnon/sqlstyle.guide/pulls/
    "SQL style guide pull requests on GitHub"
    [celko]: https://www.amazon.com/gp/product/0120887975/ref=as_li_ss_tl?ie=UTF8&linkCode=ll1&tag=treffynnon-20&linkId=9c88eac8cd420e979675c815771313d5
    "Joe Celko's SQL Programming Style (The Morgan Kaufmann Series in Data Management Systems)"
    [dl-md]: https://raw.githubusercontent.com/treffynnon/sqlstyle.guide/gh-pages/_includes/sqlstyle.guide.md
    "Download the guide in Markdown format"
    [iso-8601]: https://en.wikipedia.org/wiki/ISO_8601
    "Wikipedia: ISO 8601"
    [rivers]: http://practicaltypography.com/one-space-between-sentences.html
    "Practical Typography: one space between sentences"
    [reserved-keywords]: #reserved-keyword-reference
    "Reserved keyword reference"
    [eav]: https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model
    "Wikipedia: Entity–attribute–value model"
    [sqlstyleguide]: http://www.sqlstyle.guide
    "SQL style guide by Simon Holywell"
    [licence]: http://creativecommons.org/licenses/by-sa/4.0/
    [license]: http://creativecommons.org/licenses/by-sa/4.0/
    "Creative Commons Attribution-ShareAlike 4.0 International License"
    [sql-server-keywords]: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql
    "SQL Server reserved keywords"
    [mysql-keywords]: https://dev.mysql.com/doc/refman/8.0/en/keywords.html
    "MySQL keywords and reserved words"
  15. mattmc3 revised this gist Dec 12, 2018. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions modern_tsql_style_guide.md
    View file
    Original file line number Diff line number Diff line change
    @@ -645,6 +645,8 @@ order by 2, 1
    [mmc]: https://github.com/mattmc3
    [kickstarter-sql-guide]: https://gist.github.com/fredbenenson/7bb92718e19138c20591#file-kickstarter_sql_style_guide-md
    [editor-config]: https://EditorConfig.org
    [mssql]: https://www.microsoft.com/en-us/sql-server/default.aspx
    "Microsoft SQL Server"
    [simon]: https://www.simonholywell.com/?utm_source=sqlstyle.guide&utm_medium=link&utm_campaign=md-document
    "SimonHolywell.com"
    [issue]: https://github.com/treffynnon/sqlstyle.guide/issues
  16. mattmc3 revised this gist Dec 12, 2018. 1 changed file with 10 additions and 10 deletions.
    20 changes: 10 additions & 10 deletions modern_tsql_style_guide.md
    View file
    Original file line number Diff line number Diff line change
    @@ -214,9 +214,9 @@ select emp.id as employee_id
    , emp.last_name as employee_last_name
    , mgr.first_name as manager_first_name
    , mgr.last_name as manager_last_name
    from employees as emp
    join departments as dpt on emp.department_id = dpt.id
    left join employees as mgr on emp.manager_id = mgr.id
    from dbo.employees as emp
    join dbo.departments as dpt on emp.department_id = dpt.id
    left join dbo.employees as mgr on emp.manager_id = mgr.id
    where dpt.name in ('IT', 'DBA', 'Analytics')
    and (emp.title like '%DBA%' or emp.title like '%Database%')
    order by dpt.name
    @@ -235,9 +235,9 @@ select emp.id as employee_id
    , emp.last_name as employee_last_name
    , mgr.first_name as manager_first_name
    , mgr.last_name as manager_last_name
    from employees as emp
    join departments as dpt on emp.department_id = dpt.id
    left join employees as mgr on emp.manager_id = mgr.id
    from dbo.employees as emp
    join dbo.departments as dpt on emp.department_id = dpt.id
    left join dbo.employees as mgr on emp.manager_id = mgr.id
    where dpt.name in ('IT', 'DBA', 'Analytics')
    and (emp.title like '%DBA%' or emp.title like '%Database%')
    order by dpt.name
    @@ -249,7 +249,7 @@ select emp.id as employee_id
    -- Using a river can be tedious, so if this alignment is not preferred by your
    -- team, then standard 4 space indenting should be used in place of a river
    -- with the major keywords occupying their own line. (i.e. `select`, `from`,
    -- `where`, `group by`, `having`, `order by`):
    -- `where`, `group by`, `having`, `order by`):
    select
    emp.id as employee_id
    ,dpt.name as department
    @@ -259,9 +259,9 @@ select
    ,mgr.first_name as manager_first_name
    ,mgr.last_name as manager_last_name
    from
    employees as emp
    inner join departments as dpt on emp.department_id = dpt.id
    left join employees as mgr on emp.manager_id = mgr.id
    dbo.employees as emp
    join dbo.departments as dpt on emp.department_id = dpt.id
    left join dbo.employees as mgr on emp.manager_id = mgr.id
    where
    dpt.name in ('IT', 'DBA', 'Analytics')
    and (emp.title like '%DBA%' or emp.title like '%Database%')
  17. mattmc3 revised this gist Dec 12, 2018. 1 changed file with 337 additions and 26 deletions.
    363 changes: 337 additions & 26 deletions modern_tsql_style_guide.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,7 +1,7 @@
    ---
    layout: default
    title: Modern T-SQL Style Guide
    version: 0.0.1-alpha
    version: 0.0.2-alpha
    description: A guide to writing clean, clear, and consistent T-SQL.
    ---

    @@ -23,8 +23,9 @@ code base or reference it here for anyone on the project to freely read.
    Modern T-SQL style guide created by [Matt McElheny][mmc] is licensed under a
    [Creative Commons Attribution-ShareAlike 4.0 International License][licence].

    Based on a work at [http://www.sqlstyle.guide][sqlstyleguide] and [Kickstarter
    guide][kickstarter-sql-guide].
    Based on various SQL standards including
    [http://www.sqlstyle.guide][sqlstyleguide] and
    [Kickstarter guide][kickstarter-sql-guide].

    This document is to be used as a guide for anyone who would like to write clean
    and clear T-SQL code that is meant to be shared.
    @@ -95,19 +96,11 @@ delete g

    ### General guidance

    * Do not SHOUTCASE SQL keywords (e.g., prefer `select`, not `SELECT`)
    * Do not SHOUTCASE SQL keywords (e.g., prefer `select`, not `select`)
    * Favor using a "river" for vertical alignment so that a query can be quickly
    and easily be scanned by a new reader.
    * Names should be `underscore_separated` or `PascalCase` but do not mix styles.

    __GOOD__:
    `select count(*) as the_tally, sum(*) as the_total`

    __BAD__:
    `SELECT Count(*) as TheTally, sum(*) as theTotal`

    * Comments should as the top of your query, and should explain the intent of the
    query, not the mechanics.
    * Comments should appear at the top of your query, and should explain the intent
    of the query, not the mechanics.
    * Try to comment things that aren't obvious about the query (e.g., why a
    particular filter is necessary, why an optimization trick was needed, etc.)
    * Favor being descriptive over terseness:
    @@ -118,12 +111,65 @@ delete g
    __BAD__:
    `select usr.user_name as custnm`

    * Do not use object prefixes or Hungarian notation (e.g. `sp_`, `prc_`, `vw_`,
    `tbl`, `t_`, `fn_`, etc).
    * Follow any existing style in the script before applying this style guide.
    The SQL script should have one clear style, and these rules should not be
    applied to existing scripts unless the whole script is being changed to
    adhere to this style guide.
    * Favor storing `datetime` and `datetime2` in UTC unless embedding timezone
    information (`datetimeoffset`) so that times are clear and convertible.
    Use [ISO-8601][iso-8601] compliant time and date information
    (`YYYY-MM-DD HH:MM:SS.SSSSS`) when referring to date/time data.

    ### Naming guidance

    * Names should be `underscore_separated` or `PascalCase` but do not mix styles.

    __GOOD__:
    `select count(*) as the_tally, sum(*) as the_total`

    __BAD__:
    `select count(*) as TheTally, sum(*) as theTotal`

    #### Tables

    * Do not use [reserved words][sql-server-keywords] for table names if possible.
    * Prefer the shortest commonly understood words to name a table.
    * Naming a table as a plural makes the table easier to speak about. (e.g.
    favor `employees` over `employee`)
    * Do not use object prefixes or Hungarian notation (e.g. `sp_`, `prc_`, `vw_`,
    `tbl`, `t_`, `fn_`, etc).
    * Never give a table the same name as one of its columns and vice versa.
    * Use `_xref_` for many-to-many tables rather than concatenating table names:

    __GOOD__:
    `drivers_xref_cars`

    __BAD__:
    `drivers_cars`

    * Tables should always have a primary key. Surrogate, auto-number identity
    primary keys are preferable in most circumstances. Natural keys can be
    enforced with unique constraints in lieu of making them a primary key.

    #### Columns

    * Do not use [reserved words][sql-server-keywords] for column names if possible.
    * Prefer not simply using `id` as the primary identifier for the table if
    possible.
    * Do not add a column with the same name as its table and vice versa.
    * Avoid common words like `name`, `description`, etc. Prefer a descriptive
    prefix for those words so that they don't require aliases when joined to other
    tables with similarly named columns.

    #### Aliasing or correlations

    * Should relate in some way to the object or expression they are aliasing.
    * as a rule of thumb the correlation name should be the first letter of each
    word in the object's name.
    * If there is already a correlation with the same name then append a number.
    * Always include the `as` keyword—makes it easier to read as it is explicit.
    * For computed data (`sum()` or `avg()`) use the name you would give it were it
    a column defined in the schema.

    ### Whitespace

    @@ -227,7 +273,7 @@ order by

    ### `select` clause

    Select the first column on the same line, and align all additional columns to
    select the first column on the same line, and align all additional columns to
    the first column on their own line:

    ```sql
    @@ -306,17 +352,31 @@ select prj.name project_name
    ...
    ```

    `case` statements should be aligned with a river after the `case` keyword, with
    `when`, `then`, and `end` on their own lines.
    Do not bracket-escape table or column names unless the names contain keyword
    collisions or would cause a syntax error without properly qualifying them.

    __GOOD__:

    ```sql
    select prj.name as project_name
    , prj.created_at
    , case when prj.category in ('stuff', 'and', 'things')
    then 'Amazing!'
    else 'Average'
    end as opinion
    from ...
    -- name and state are keywords
    select [name]
    , street1
    , city
    , [state]
    , zip
    from dbo.people
    ```

    __BAD__:

    ```sql
    -- the brackets are messy and unnecessary
    select [name]
    , [street1]
    , [city]
    , [state]
    , [zip]
    from [dbo].[people]
    ```

    Long Window functions should be split across multiple lines: one for each
    @@ -331,6 +391,257 @@ row_number() over (partition by prj.category_id, prj.year
    ) as sequence_number
    ```

    ### `case` statements

    `case` statements aren't always easy to format but try to align `when`, `then`,
    and `else` together inside `case` and `end`.

    `then` can stay on the `when` line if needed, but aligning with `else` is
    preferable.

    ```sql
    select prj.name as project_name
    , prj.created_at
    , case when prj.category in ('stuff', 'and', 'things')
    then 'Amazing!'
    else 'Average'
    end as opinion
    from ...
    ```

    ### `from` and `join` clause

    Only one table should be in the `from`. Never use comma separated `from`-joins:

    __GOOD__:

    ```sql
    select projects.name as project_name
    , count(backings.id) as backings_count
    from dbo.projects as projects
    join dbo.backings as backings on backings.project_id = projects.id
    ...
    ```

    __BAD__:

    ```sql
    select projects.name as project_name
    , count(backings.id) as backings_count
    from dbo.projects as projects, dbo.backings as backings
    where backings.project_id = projects.id
    ...
    ```

    Favor not using the extraneous words `inner` or `outer` when joining tables.
    Alignment is easier without them, they don't add to the understanding of the
    query, and the full table list is easier to scan without excessive staggering:

    __GOOD__:

    ```sql
    select projects.name as project_name,
    , count(backings.id) as backings_count
    from dbo.projects as projects
    join dbo.backings as backings on ...
    join ...
    left join dbo.backer_rewards as backer_rewards on ...
    left join ...
    ```

    __BAD__:

    ```sql
    select projects.name as project_name,
    , count(backings.id) as backings_count
    from dbo.projects as projects
    inner join dbo.backings as backings on ...
    inner join ...
    left outer join dbo.backer_rewards as backer_rewards on ...
    left outer join ...
    ```

    The `on` keyword and condition goes on the `inner join` line:

    ```sql
    select projects.name as project_name
    , count(backings.id) as backings_count
    from dbo.projects as projects
    join dbo.backings as backings on projects.id = backings.project_id
    ...
    ```

    Additional filters in the `join` go on new indented lines. Line up using the
    `on` keyword as a second river:

    ```sql
    select projects.name as project_name
    , count(backings.id) as backings_count
    from dbo.projects as projects
    join dbo.backings as backings on projects.id = backings.project_id
    and backings.project_country <> 'US'
    ...
    ```

    Begin with `inner join`s and then list `left join`s, order them semantically,
    and do not intermingle `left join`s with `inner join`s unless necessary:

    __GOOD__:

    ```sql
    join dbo.backings as backings on ...
    join dbo.users as users on ...
    join dbo.locations as locations on ...
    left join dbo.backer_rewards as backer_rewards on ...
    left join ...
    ```

    __BAD__:

    ```sql
    left join dbo.backer_rewards as backer_rewards on backings
    join dbo.users as users on ...
    left join ...
    join dbo.locations as locations on ...
    ```

    Avoid `right joins` as they are usually better written with a `left join`

    __BAD__:

    ```sql
    from dbo.backer_rewards as backer_rewards
    right join dbo.users as users on ...
    ```

    __GOOD__:

    ```sql
    from dbo.users as users
    left join dbo.backer_rewards as backer_rewards on ...
    ```

    ### `where` clause

    Multiple `where` clauses should go on different lines and align to the river:

    ```sql
    select name
    , goal
    from dbo.projects as projects
    where country = 'US'
    and deadline >= '2015-01-01'
    ...
    ```

    When mixing `and` and `or` statements, do not rely on order of operations and
    instead always use parenthesis to make the intent clear:

    ```sql
    select name
    , goal
    from dbo.projects as projects
    where (country = 'US'
    and deadline >= '2015-01-01')
    or include = 1
    ...
    ```

    Always put a semicolon on its own line when using them. This prevents common
    errors like adding conditions to a `where` clause and neglecting to move the
    trailing semicolon:

    __BAD__:

    ```sql
    -- messing with a where clause, it's easy to miss a trailing semicolon
    delete u
    from dbo.users u
    where created_at < dateadd(year, -1, getutcdate());
    and user_name like '%test_user%' -- whoops, missed that trailing semicolon!
    ...
    ```

    __GOOD__:

    ```sql
    -- semicolons that start a line are easy to spot
    delete u
    from dbo.users u
    where created_at < dateadd(year, -1, getutcdate())
    and user_name like '%test_user%'
    ; -- < if we add a condition, we'll see this semicolon
    ...
    ```

    ### `group by` clause

    Maintain the same column order as the `select` clause in the `group by`:

    __BAD__:

    ```sql
    -- messing with the 'group by' order makes it hard to scan for accuracy
    select p.project_name
    , p.country
    , sum(p.pledged) as total_pledged
    from dbo.projects as p
    group by p.country -- out of order
    , p.project_name
    ```

    __GOOD__:

    ```sql
    select p.project_name
    , p.country
    , sum(p.pledged) as total_pledged
    from dbo.projects as p
    group by p.project_name
    , p.country
    ```

    ### `order by` clause

    Do not use the superfluous `asc` in `order by` statements:

    __BAD__:

    ```sql
    -- asc is clutter - it's never ambiguous when you wanted to sort ascending
    select p.project_name
    , p.country
    from dbo.projects as p
    order by p.country asc
    , p.project_name asc
    ```

    __GOOD__:

    ```sql
    -- asc is clutter - it's never ambiguous when you wanted to sort ascending
    select p.project_name
    , p.country
    from dbo.projects as p
    order by p.country
    , p.project_name
    ```

    Ordering by column number is okay, but not preferred:

    ```sql
    -- This is okay, but not great. It's better when running ad-hoc queries or
    -- queries with calculated columns
    select p.project_name
    , case when p.category in ('Science', 'Technology', 'Engineering')
    then 'STEM'
    else 'Other'
    end as category_group
    , p.country
    from dbo.projects as p
    order by 2, 1
    ```

    [mmc]: https://github.com/mattmc3
    [kickstarter-sql-guide]: https://gist.github.com/fredbenenson/7bb92718e19138c20591#file-kickstarter_sql_style_guide-md
    [editor-config]: https://EditorConfig.org
  18. mattmc3 created this gist Dec 12, 2018.
    364 changes: 364 additions & 0 deletions modern_tsql_style_guide.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,364 @@
    ---
    layout: default
    title: Modern T-SQL Style Guide
    version: 0.0.1-alpha
    description: A guide to writing clean, clear, and consistent T-SQL.
    ---

    # Modern T-SQL Style Guide

    ## Purpose

    You can use this set of guidelines, fork them, or make your own - the key here
    is that you pick a style and stick to it. The odds of making everyone happy
    are low, so compromise is a guiding principle to achieve cohesion.

    These guidelines are designed to make T-SQL statements easy to write, easy to
    read, and easy to maintain. This guide is opinionated in some areas and relaxed
    in others.

    It is easy to include this guide in Markdown format as a part of a project's
    code base or reference it here for anyone on the project to freely read.

    Modern T-SQL style guide created by [Matt McElheny][mmc] is licensed under a
    [Creative Commons Attribution-ShareAlike 4.0 International License][licence].

    Based on a work at [http://www.sqlstyle.guide][sqlstyleguide] and [Kickstarter
    guide][kickstarter-sql-guide].

    This document is to be used as a guide for anyone who would like to write clean
    and clear T-SQL code that is meant to be shared.

    **NOTE**: This style guide is written for use with [Microsoft SQL
    Server][mssql], but much of it can be applied to any SQL database with some
    simple modifications.

    ## Principles

    * We take a disciplined and practical approach to writing code.
    * We treat T-SQL like any other source code, which should be checked into
    source control, peer reviewed, and properly maintained.
    * We believe consistency in style is important, and we value craftsmanship, but
    not to the exclusion of other practical concerns.
    * We demonstrate intent explicitly in code, via clear structure and comments
    where needed.

    ## Quick look

    Before getting into all the rules, here is a quick look at some examples showing
    well formatted SQL that matches the recommendations in this style guide:

    ```sql
    -- basic select example
    select u.user_name as employee_user_name
    , u.created as created_date
    , d.[name] as department_name
    from dbo.users as u
    join dbo.departments as d on u.department_id = d.id
    where u.created >= dateadd(year, -1, getutcdate())
    order by created_date desc
    ```

    ```sql
    -- basic insert example
    insert into dbo.categories (
    category_code
    ,category_name
    ,created_at
    )
    values (
    'MOV'
    ,'Movies'
    ,getutcdate()
    )
    ```

    ```sql
    -- basic update example
    update e
    set e.salary = e.salary * 1.04
    , updated_at = getutcdate()
    from dbo.employees as e
    where e.department_id in (1, 2, 3)
    ```

    ```sql
    -- basic delete example
    delete g
    from dbo.gifts as g
    where g.is_purchased = 1
    and g.updated_at <= dateadd(month, -6, getutcdate())
    and g.return_date is null
    ```

    ## Rules

    ### General guidance

    * Do not SHOUTCASE SQL keywords (e.g., prefer `select`, not `SELECT`)
    * Favor using a "river" for vertical alignment so that a query can be quickly
    and easily be scanned by a new reader.
    * Names should be `underscore_separated` or `PascalCase` but do not mix styles.

    __GOOD__:
    `select count(*) as the_tally, sum(*) as the_total`

    __BAD__:
    `SELECT Count(*) as TheTally, sum(*) as theTotal`

    * Comments should as the top of your query, and should explain the intent of the
    query, not the mechanics.
    * Try to comment things that aren't obvious about the query (e.g., why a
    particular filter is necessary, why an optimization trick was needed, etc.)
    * Favor being descriptive over terseness:

    __GOOD__:
    `select usr.user_name as customer_user_name`

    __BAD__:
    `select usr.user_name as custnm`

    * Do not use object prefixes or Hungarian notation (e.g. `sp_`, `prc_`, `vw_`,
    `tbl`, `t_`, `fn_`, etc).
    * Follow any existing style in the script before applying this style guide.
    The SQL script should have one clear style, and these rules should not be
    applied to existing scripts unless the whole script is being changed to
    adhere to this style guide.

    ### Whitespace

    * No tabs. Use spaces for indents.
    * Configure your editor to 4 spaces per indent, but prefer your SQL to indent
    to the "river" and not to a set indent increment.
    * No trailing whitespace.
    * No more than two lines between statements.
    * No empty lines in the middle of a single statement.
    * One final newline at the end of a file
    * Use an [.editorConfig file][editor-config] to enforce reasonable whitespace rules if your T-SQL
    editor supports it:

    ```ini
    # .editorConfig is awesome: https://EditorConfig.org

    # SQL files
    [*.{sql,tsql,ddl}]
    charset = utf-8
    indent_style = space
    indent_size = 4
    end_of_line = crlf
    trim_trailing_whitespace = true
    insert_final_newline = true
    ```

    ### River formatting

    Spaces should be used to line up the code so that the root keywords all end on
    the same character boundary. This forms a "river" down the middle making it easy
    for the readers eye to scan over the code and separate the keywords from the
    implementation detail. Rivers are [bad in typography][rivers], but helpful here.
    [Celko's book][celko] describes using a river to vertically align your query.
    Right align keywords to the river.

    ```sql
    -- a river in the 7th column helps vertical readability
    select emp.id as employee_id
    , dpt.name as department
    , dpt.location
    , emp.first_name as employee_first_name
    , emp.last_name as employee_last_name
    , mgr.first_name as manager_first_name
    , mgr.last_name as manager_last_name
    from employees as emp
    join departments as dpt on emp.department_id = dpt.id
    left join employees as mgr on emp.manager_id = mgr.id
    where dpt.name in ('IT', 'DBA', 'Analytics')
    and (emp.title like '%DBA%' or emp.title like '%Database%')
    order by dpt.name
    , emp.last_name
    , emp.first_name
    ```

    ```sql
    -- alternately, a river in the a different column is fine if that is preferred
    -- due to longer keywords, but know that indenting can feel off if the `select`
    -- is not in the first column for the query
    select emp.id as employee_id
    , dpt.name as department
    , dpt.location
    , emp.first_name as employee_first_name
    , emp.last_name as employee_last_name
    , mgr.first_name as manager_first_name
    , mgr.last_name as manager_last_name
    from employees as emp
    join departments as dpt on emp.department_id = dpt.id
    left join employees as mgr on emp.manager_id = mgr.id
    where dpt.name in ('IT', 'DBA', 'Analytics')
    and (emp.title like '%DBA%' or emp.title like '%Database%')
    order by dpt.name
    , emp.last_name
    , emp.first_name
    ```

    ```sql
    -- Using a river can be tedious, so if this alignment is not preferred by your
    -- team, then standard 4 space indenting should be used in place of a river
    -- with the major keywords occupying their own line. (i.e. `select`, `from`,
    -- `where`, `group by`, `having`, `order by`):
    select
    emp.id as employee_id
    ,dpt.name as department
    ,dpt.location
    ,emp.first_name as employee_first_name
    ,emp.last_name as employee_last_name
    ,mgr.first_name as manager_first_name
    ,mgr.last_name as manager_last_name
    from
    employees as emp
    inner join departments as dpt on emp.department_id = dpt.id
    left join employees as mgr on emp.manager_id = mgr.id
    where
    dpt.name in ('IT', 'DBA', 'Analytics')
    and (emp.title like '%DBA%' or emp.title like '%Database%')
    order by
    dpt.name
    ,emp.last_name
    ,emp.first_name
    ```

    ### `select` clause

    Select the first column on the same line, and align all additional columns to
    the first column on their own line:

    ```sql
    select projects.name
    , users.email
    , projects.country
    , count(backings.id) as backings_count
    from ...
    ```

    Use commas as a prefix as opposed to a suffix. This is preferred because:

    * It makes it easy to add new columns to the end of the column list
    * It prevents unintentional aliasing bugs
    * It makes commenting out columns much easier
    * Column lists are more likely to change at the end than at the beginning

    The comma should border the "river".

    __GOOD__:

    ```sql
    select user_name as customer_user_name
    , created_at
    , is_deleted
    ...
    ```

    __BAD__:

    ```sql
    -- whoops! forgot a trailing comma because it's hard to see, making an
    -- accidental alias
    select usr.user_name as customer_user_name,
    created_at
    is_deleted
    ...
    ```

    Always rename aggregates, `case` derived columns, and function-wrapped columns:

    ```sql
    select product_name
    , sum(amount) as sum_amount
    from ...
    ```

    Always use table aliases for all columns when querying from more than one table.
    Single character aliases are fine for a few tables, but are less likely to be
    clear as a query grows:

    ```sql
    select prj.name as project_name
    , count(bck.id) as backings_count
    from dbo.backings as bck
    join dbo.projects as prj
    on ...
    ```

    Always use `as` to rename columns. `as` statements can be used for additional
    vertical alignment but don't have to be:

    __GOOD__:

    ```sql
    select projects.name as project_name
    , count(backings.id) as backings_count
    ...
    ```

    __BAD__:

    ```sql
    select prj.name project_name
    , count(bck.id) backings_count
    ...
    ```

    `case` statements should be aligned with a river after the `case` keyword, with
    `when`, `then`, and `end` on their own lines.

    ```sql
    select prj.name as project_name
    , prj.created_at
    , case when prj.category in ('stuff', 'and', 'things')
    then 'Amazing!'
    else 'Average'
    end as opinion
    from ...
    ```

    Long Window functions should be split across multiple lines: one for each
    clause, aligned to the `partition` keyword. Partition keys can share the same
    line, or be split. Ascending order is an intuitive default and thus using an
    explicit `asc` is not necessary whereas `desc` is. All window functions should
    be aliased.

    ```sql
    row_number() over (partition by prj.category_id, prj.year
    order by pledged desc, last_name, first_name
    ) as sequence_number
    ```

    [mmc]: https://github.com/mattmc3
    [kickstarter-sql-guide]: https://gist.github.com/fredbenenson/7bb92718e19138c20591#file-kickstarter_sql_style_guide-md
    [editor-config]: https://EditorConfig.org
    [simon]: https://www.simonholywell.com/?utm_source=sqlstyle.guide&utm_medium=link&utm_campaign=md-document
    "SimonHolywell.com"
    [issue]: https://github.com/treffynnon/sqlstyle.guide/issues
    "SQL style guide issues on GitHub"
    [fork]: https://github.com/treffynnon/sqlstyle.guide/fork
    "Fork SQL style guide on GitHub"
    [pull]: https://github.com/treffynnon/sqlstyle.guide/pulls/
    "SQL style guide pull requests on GitHub"
    [celko]: https://www.amazon.com/gp/product/0120887975/ref=as_li_ss_tl?ie=UTF8&linkCode=ll1&tag=treffynnon-20&linkId=9c88eac8cd420e979675c815771313d5
    "Joe Celko's SQL Programming Style (The Morgan Kaufmann Series in Data Management Systems)"
    [dl-md]: https://raw.githubusercontent.com/treffynnon/sqlstyle.guide/gh-pages/_includes/sqlstyle.guide.md
    "Download the guide in Markdown format"
    [iso-8601]: https://en.wikipedia.org/wiki/ISO_8601
    "Wikipedia: ISO 8601"
    [rivers]: http://practicaltypography.com/one-space-between-sentences.html
    "Practical Typography: one space between sentences"
    [reserved-keywords]: #reserved-keyword-reference
    "Reserved keyword reference"
    [eav]: https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model
    "Wikipedia: Entity–attribute–value model"
    [sqlstyleguide]: http://www.sqlstyle.guide
    "SQL style guide by Simon Holywell"
    [licence]: http://creativecommons.org/licenses/by-sa/4.0/
    "Creative Commons Attribution-ShareAlike 4.0 International License"
    [sql-server-keywords]: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql
    "SQL Server reserved keywords"
    [mysql-keywords]: https://dev.mysql.com/doc/refman/8.0/en/keywords.html
    "MySQL keywords and reserved words"