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.
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'
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 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
Any field that is ambiguous on its use outside the table should be aliased with the
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
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.
In this short style guide, we covered some basic syntax formatting rules for SQL that can help build a more cohesive code base.