public

SQL Style Guide

As it is a whitespace insensitive language, SQL does not have a strict formatting required of its syntax. Below is a simple style guide to help get cohesion on a project.

6 months ago

Latest Post NEW - ByteUnits Membership by Tyler Moon public

SQL (Structured Query Language) is a programming language designed for managing data in databases. It is particularly useful for querying and manipulating structured data with relations between entities. SQL was originally developed in 1970 with relational algebra and tuple relational calculus in mind, but over the years has been iterated on to make it more user friendly.

As it is a whitespace insensitive language, SQL does not have a strict formatting required of its syntax. This has let to many different styles being adopted, which if in the same project can make it difficult to transition from one stored procedure or query to another. Below is a simple style guide to help get cohesion on a project. This is by all means not a comprehensive list and is somewhat subjective and as such should be taken with a grain of salt.

Indentation

As a general rule of thumb all SQL should be space delimited and not tab delimited. Any statements inside a Common Table Expression (aka. CTE) should be indented 4 spaces.

-- this
WITH data AS (
    SELECT * 
    FROM dto.sometable
)

-- not this
WITH bad_data AS (
 SELECT * 
 FROM dto.sometable
)

Any indentation within a single query should be only 2 spaces.

-- this
SELECT
  column_name
FROM table_name
WHERE column_name = 'test1'

When using SELECT each column should have its own row.

-- this
SELECT
  column_1,
  column_2
FROM table_name
WHERE column_1 = column_2

-- not this
SELECT column_1, column_2 FROM table_name WHERE column_1 = column_2

Field Names

Field names should be consistent. If using Pascal case then only use Pascal. If using all lowercase with underscores (also called "snake" case) then only use that.

-- this
SELECT
  column_the_first,
  column_the_second,
FROM table_name
WHERE x = y

-- not
SELECT
  ColumnTheFirst,
  second_column,
FROM table_name
WHERE x = y

The SQL keywords (SELECT, WHERE, ect) should be in all caps. This is commonly understood as a requirement, however it is not rather just a convention. This convention helps the SQL keywords to stick out from the rest of the query. One exception to this rule could be made if everyone on the team is using SSMS or some other standard SQL editor which auto highlights the keywords. In this case either all caps or all lowercase should be decided upon and stuck to.

SELECT 
  *
FROM table_name

Aliasing

Any field that is ambiguous on its use outside the table should be aliased with the AS keyword

SELECT
  e.id   AS example_id,
  e.name AS example_id
FROM example AS e

Table aliases should be as short as possible to keep queries readable.

-- this
SELECT
  e.id   AS example_id,
  e.name AS example_id
FROM example AS e

-- not this
SELECT
  exampletablealias.id,
  exampletablealias.name
FROM example as exampletablealias

Comments

There are two syntaxes for comments and they should be used appropriately.

-- this is a single line comment, but should be less than 100 charaters
/*
  this is a multi-line comment for when there is more to say  than will
  fit in a single line. Especially useful for giving a description of a
  stored procedure or function
*/

Additional Style Guides

As mentioned earlier this is an opinionated style guide, and a very brief one. Below is a list of other more in-depth style guides that may be useful if you need more information.

Summary

In this short style guide, we covered some basic syntax formatting rules for SQL that can help build a more cohesive code base.

Tyler Moon

Published 6 months ago