SQL formatting guidelines

Well formatted code is easier to read and to maintain, resulting in a higher overall quality. There are many good ways to format SQL code. This document provides some guidelines we use at Thinkwise to write legible and structured SQL code.

The guidelines are structured per statement. All guidelines are clarified with an example.

General guidelines

• Use 4 spaces instead of tabs.
• Indent using a multiple of 4 spaces.
• Align opening and closing keywords (begin and end, case and end, etc.).
• Do not use empty lines inside a single statement.
• Place commas in front of the column names.

SELECT

1. Left align the select, from, where, order by, having and group by keywords.
2. Place the select list under the select keyword and indent using 4 spaces.
3. Provide an alias for all columns without a name (constants, functions, composite columns), using the as keyword.
4. Provide an alias for all tables, consisting of the first letter of every subname, without using the as keyword. If this is not sufficient, add a number or choose another meaningful alias.
5. Place composite or calculated columns on one line, unless the the line is too long.

Example SELECT

select
    si.sales_invoice_id
    ,si.customer_id
    ,si.invoice_date
    ,getdate() as due_date
    ,si.amount_excl_vat
    ,si.amount_incl_vat
    ,1 as invoice_status
from sales_invoice si

select
    concat(e.last_name, ' ', e.first_name) as name
    ,e.email
from employee e

tip

Some people prefer to provide an alias for all columns. Be sure to left align the aliases if you choose to do so.

ORDER BY and GROUP BY

1. Place the order by or group by list under the order by or group by keyword and indent using 4 spaces.

Example ORDER BY

select
    si.sales_invoice_id
    ,si.customer_id
    ,si.invoice_date
    ,getdate() as due_date
    ,si.amount_excl_vat
    ,si.amount_incl_vat
    ,1 as invoice_status
from sales_invoice si
order by
    si.customer_id
    ,si.invoice_date
    ,si.invoice_status

Example GROUP BY

select
    p.project_id
    ,h.date
    ,avg(h.number_of_hours) as avg_number_of_hours
from project p
join hour h
  on h.project_id  = p.project_id
group by
    p.project_id
    ,p.description
    ,h.date

WHERE and HAVING

1. Right align the top level and keywords with the where or having keyword.
2. Place and keywords in front of the condition.
3. Place or keywords on a separate line, left aligned with the previous line.
4. Always use parentheses around or conditions.
5. Indent conditions inside parentheses, using a multiple of 4 spaces.
6. Left align the closing parentheses ) with the condition (e.g. and) of the opening parentheses.
7. Align comparison operators (=, <, etc.) for conditions of the same level.

Example WHERE

select
    si.sales_invoice_id
    ,si.customer_id
    ,si.invoice_date
    ,getdate() as due_date
    ,si.amount_excl_vat
    ,si.amount_incl_vat
    ,1 as invoice_status
from sales_invoice si
where si.invoice_date = '2019-1-1'
  and si.customer_id  = 15
  and (
    si.invoice_status  = 1
    or
    si.amount_excl_vat > 10.000
  )

Example HAVING

select
    p.project_id
    ,h.date
    ,avg(h.number_of_hours) as avg_number_of_hours
    ,max(h.number_of_hours) as max_number_of_hours
from project p
join hour h
  on h.project_id  = p.project_id
group by
    p.project_id
    ,p.description
    ,h.date
having avg(h.number_of_hours) > 5
   and max(h.number_of_hours) < 12

CASE expressions

1. Align the case and the end keywords.
2. Indent the when and else expressions, using a multiple of 4 spaces.
3. Place the then expression on the same line as the when, unless the line is too long.
4. When the line is too long, place the then keyword on a new line and indent using 4 spaces.

Example simple CASE

select
    so.sales_order_id
    ,case so.order_status
        when 0 then 'not_approved'
        when 1 then 'approved'
        when 2 then 'sent'
        else 'delivered'
     end as order_status
    ,so.customer_id
from sales_order so

Example searched CASE

select
    so.sales_order_id
    ,case
        when so.order_status = 0 or so.order_status is null
            then 'not_approved'
        when so.order_status = 1 then 'approved'
        when so.order_status = 2 then 'sent'
        else 'delivered'
     end as order_status
    ,so.customer_id
from sales_order so

JOIN

1. Prevent the use of right joins.
2. Don't use inner for regular (inner) joins or outer for left joins.
3. Left align the join, left join and cross join keywords.
4. Right align the on and and keywords with the join keyword.
5. Align comparison operators (=, <, etc.) for join conditions.
6. Place the columns of the joined table on the left side of the comparison.

Example JOIN

select
    p.description
    ,sp.name
    ,h.number_of_hours
from project p
join sub_project sp
  on sp.project_id = p.project_id
join hour h
  on h.project_id     = sp.project_id
 and h.sub_project_id = sp.sub_project_id

Example LEFT JOIN

select
    p.description
    ,sp.name
from project p
left join sub_project sp
  on sp.project_id = p.project_id

UNION

1. Left align the union or union all keyword.
2. Place empty lines before and after the union keyword.
3. Use comments to describe the select statements.

Example UNION ALL

--Approved sales invoices
select
    si.sales_invoice_id
    ,'Approved' as status
from sales_invoice si
where si.invoice_status = 1 --Approved

union all

--Not approved sales invoices
select
    si.sales_invoice_id
    ,'Not Approved' as status
from sales_invoice si
where si.invoice_status = 0 --Not approved

Functions

1. Place function calls on a single line, unless the line is too long.
2. When the line is too long, place the parameters on a new line and indent using 4 spaces
3. Left align the closing parentheses ) with the function name (e.g. datediff).

Example FUNCTION

select
    si.sales_invoice_id
    ,si.invoice_date
    ,si.due_date
    ,datediff(day, si.invoice_date, si.due_date) as number_of_days
from sales_invoice si

Example FUNCTION with many parameters

select
    si.sales_invoice_id
    ,si.invoice_date
    ,si.due_date
    ,datediff(
        day
        ,si.invoice_date
        ,si.due_date
     ) as number_of_days
    ,si.invoice_status
from sales_invoice si

Subqueries

1. Consider using apply instead of subqueries to improve readability. Use cross apply for regular (inner) joins and outer apply for left joins.
2. Indent subqueries relative to the opening parenthesis or the apply keyword, using a multiple of 4 spaces.
3. Align the closing parentheses ) with the opening parentheses (.

Example subquery in SELECT

tip

💡 Use OUTER APPLY instead

select
    p.project_id
    ,(
        select sum(h.number_of_hours)
        from hour h
        where h.project_id = p.project_id
     ) as number_of_hours
from project p

Alternative using OUTER APPLY

select
    p.project_id
    ,s.number_of_hours
from project p
outer apply (
        select sum(h.number_of_hours) as number_of_hours
        from hour h
        where h.project_id = p.project_id
    ) s

Example subquery in FROM

tip

Use CROSS APPLY instead

select
    p.project_id
    ,h.number_of_hours
from project p
join (
        select
            h.project_id
            ,sum(h.number_of_hours) as number_of_hours
        from hour h
        group by h.project_id
    ) h
  on h.project_id = p.project_id

Alternative using CROSS APPLY

select
    p.project_id
    ,s.number_of_hours
from project p
cross apply (
        select sum(h.number_of_hours) as number_of_hours
        from hour h
        where h.project_id = p.project_id
    ) s

Example subquery in WHERE

tip

Use CROSS or OUTER APPLY instead

select p.project_id           as project_id
from project p
where p.finished   = 0
  and 100         >= (
                         select sum(h.number_of_hours) as number_of_hours
                         from hour h
                         where h.project_id = p.project_id
                     )

Example using CROSS APPLY

select p.project_id           as project_id
from project p
cross apply (
        select sum(h.number_of_hours) as number_of_hours
        from hour h
        where h.project_id = p.project_id
    ) s
where p.finished   = 0
  and s.number_of_hours < 100

IN and EXISTS

1. Use in with constant values only and exists with subqueries.
2. Indent subqueries relative to the exists keyword, using a multiple of 4 spaces.
3. Left align the closing parentheses ) with the exists keyword.

Example EXISTS

select p.description
from project p
where exists (
        select 1
        from sub_project sp
        where sp.project_id = p.project_id
      )

Example IN

select p.description
from project p
where p.status in (1, 2, 3) --new, open, closed

INSERT

1. Don't use the into keyword.
2. Always use a column list.
3. Place the column list under the insert keyword and indent using 4 spaces.
4. Left align the closing parentheses with the insert keyword.
5. Left align the select or values keyword with the insert keyword.

Example

insert project (
    customer_id
    ,description
    ,planned_start_date
    ,planned_end_date
    ,actual_start_date
    ,finished
    ,finished_on_date
)
select
    p.customer_id
    ,p.description
    ,p.planned_start_date
    ,p.planned_end_date
    ,null as actual_start_date
    ,0 as finished
    ,null as finished_on_date
from project p
where p.project_id = 3

insert project (
    customer_id
    ,description
)
values (
    (1, 'project 1')
    ,(2, 'project 2')
    ,(3, 'project 3')
)

UPDATE

1. Use a from-clause with joins instead of subqueries.
2. Always use the alias of the table to update in the update statement.
3. Left align the set keyword with the update keyword.
4. Place the column list after the set keyword and indent using 4 spaces.
5. Align the assignment operators = of the column list.

Example UPDATE

update sp
set sp.finished       = p.finished
    ,finished_on_date = p.finished_on_date
from sub_project sp
join project p
  on p.project_id = sp.project_id
where p.finished = 1

DELETE

1. Use a from-clause with joins instead of subqueries.
2. Always use the alias of the table in the delete statement.

Example DELETE

delete sp
from sub_project sp
join project p
  on p.project_id = sp.project_id
where p.finished = 1

DECLARE variables

1. Declare all variables at the top of the code template.
2. Place the variable list under the declare keyword and indent using 4 spaces.
3. Place commas in front of the variable names.
4. Left align the data types for all variables.

Example DECLARE

declare
    @project_id       project_id
    ,@project_vrs_id  project_vrs_id
    ,@tab_id          tab_id

IF and WHILE

1. Always use begin and end in an if or while statement.
2. Left align the if, while, begin and end keywords.
3. Don't use empty lines after the begin and before the end keywords.
4. Do use empty lines to separate statements within begin and end blocks.
5. Left align top level and keywords with the first condition.
6. Place and keywords in front of the condition.
7. Place or keywords on a separate line, left aligned with the previous line.
8. Always use parentheses around or conditions.
9. Indent conditions inside parentheses, using a multiple of 4 spaces.
10. Align the closing parentheses ) with the opening parentheses (.
11. Align comparison operators (=, <, etc.) for conditions of the same level.

Example IF

if @project_id         = 1
   and @project_vrs_id = 'DB'
   and (
        @project_status = 3
        or
        @project_status = 5
   )
begin
    set @project_vrs_id = 'DBA'
    
    set @project_status = 6
end

Example IF with nested parentheses

if (
        (
            @project_id         = 1
            and @project_vrs_id = 'DB'
        )
        or
        @project_status = 3
   )
begin
    set @project_vrs_id = 'DBA'
end

Example WHILE

while @status       = 3
      and @counter >= 1
begin
    set @counter = @counter + 1
end

Table variables and temporary tables

1. Place the column list under the select keyword and indent using 4 spaces.
2. Place commas in front of the column names.
3. Left align the data types for all variables.

Example table variable

note

For table variables, use domains as much as possible.

declare @project table (
    project_id   project_id
    ,description description
)

Example temporary table

create table #project (
    project_id   int
    ,description varchar(200)
)

drop table #project

Common table expressions (CTEs)

1. Left align the with, as and select, update or insert keywords.
2. Place the column list under the with keyword and indent using 4 spaces.
3. Place commas in front of the column names.

Example CTE

;with sales_invoice_vat (
    sales_invoice_id
    ,vat_percentage
)
as (
    select
        sales_invoice_id
        ,100 * ((amount_incl_vat - amount_excl_vat)/amount_excl_vat) as vat_percentage
    from sales_invoice
    where amount_excl_vat <> 0
)
select
    si.sales_invoice_id,
    siv.vat_percentage
from sales_invoice si
left join sales_invoice_vat siv
  on siv.sales_invoice_id = si.sales_invoice_id

CURSOR

1. Place the cursor parameters on the same line as the declare keyword.
2. Left align the declare and select keywords.
3. Place all variables on the same line as the fetch keyword.

Example CURSOR

declare
    @country_id    id
    ,@country_name name

declare countries cursor local static read_only forward_only for
select
    c.country_id
    ,c.name
from country c
order by c.name

open countries

fetch next from countries into @country_id, @country_name
while @@fetch_status = 0
begin
    print @country_name
    print @country_id

    fetch next from countries into @country_id, @country_name
end

close countries
deallocate countries

Transactions

1. Left align the begin tran, commit tran and rollback tran keywords.
2. Left align the code within the transaction.
3. Don't name the transaction unless there are nested transactions.

Example transaction

begin tran

insert project (
    customer_id
    ,description
    ,planned_start_date
    ,planned_end_date
    ,actual_start_date
    ,finished
    ,finished_on_date
)
select
    p.customer_id
    ,p.description
    ,p.planned_start_date
    ,p.planned_end_date
    ,null as actual_start_date
    ,0 as finished
    ,null as finished_on_date
from project p
where p.project_id = 3

commit tran

TRY CATCH

1. Left align the begin try, end try, begin catch and end catch keywords.
2. Indent the code within the try and catch, using a multiple of 4 spaces.

Example TRY CATCH with transaction

begin try
    begin tran

    insert project (
        customer_id
        ,description
        ,planned_start_date
        ,planned_end_date
        ,actual_start_date
        ,finished
        ,finished_on_date
    )
    select
        p.customer_id
        ,p.description
        ,p.planned_start_date
        ,p.planned_end_date
        ,null as actual_start_date
        ,0 as finished
        ,null as finished_on_date
    from project p
    where p.project_id = 3

    commit tran
end try
begin catch
    rollback tran

    throw
end catch

Procedure calls

1. Place the parameters on the same line unless there are many parameters.
2. When there are many parameters, place the parameters on a new line and indent using 4 spaces.
3. Place commas in front of the parameters.
4. Align the output keywords.

Example procedure call

exec task_kopieer_project @project_id

Example with output parameters

exec task_kopieer_project
    @project_id
    ,@klant_id
    ,@datum
    ,@verwachte_kosten    output
    ,@verwachte_einddatum output

Comments

1. Use -- for single line comments and /* ... */ for multiline comments.

   To quickly comment or uncomment a block of code for debugging purposes, select the code and use your editors' shortcut.
   For SQL Server Management Studio and Azure Data Studio, this is Ctrl+K,C and Ctrl+K,U.

2. Don't describe what code used to do or what has changed.
3. Don't leave commented-out code in templates.

Example comments

/*
This is an example
of multiline comment
*/

-- Update today if it is different from the current date
if exists (
        select 1
        from settings i
        where i.today <> cast(getdate() as date)
   )
begin
    update settings
    set today = cast(getdate() as date)
end