title | sidebar_label |
---|---|
SQL formatting guidelines |
📖 SQL formatting |
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.
- Use 4 spaces instead of tabs.
- Indent using a multiple of 4 spaces.
- Align opening and closing keywords (
begin
andend
,case
andend
, etc.). - Do not use empty lines inside a single statement.
- Place commas in front of the column names.
- Left align the
select
,from
,where
,order by
,having
andgroup by
keywords. - Place the select list under the
select
keyword and indent using 4 spaces. - Provide an alias for all columns without a name (constants, functions, composite columns), using the
as
keyword. - 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. - Place composite or calculated columns on one line, unless the the line is too long.
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
Some people prefer to provide an alias for all columns. Be sure to left align the aliases if you choose to do so.
- Place the order by or group by list under the
order by
orgroup by
keyword and indent using 4 spaces.
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
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
- Right align the top level
and
keywords with thewhere
orhaving
keyword. - Place
and
keywords in front of the condition. - Place
or
keywords on a separate line, left aligned with the previous line. - Always use parentheses around
or
conditions. - Indent conditions inside parentheses, using a multiple of 4 spaces.
- Left align the closing parentheses
)
with the condition (e.g.and
) of the opening parentheses. - Align comparison operators (
=
,<
, etc.) for conditions of the same level.
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
)
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
- Align the
case
and theend
keywords. - Indent the
when
andelse
expressions, using a multiple of 4 spaces. - Place the
then
expression on the same line as thewhen
, unless the line is too long. - When the line is too long, place the
then
keyword on a new line and indent using 4 spaces.
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
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
- Prevent the use of right joins.
- Don't use
inner
for regular (inner) joins orouter
for left joins. - Left align the
join
,left join
andcross join
keywords. - Right align the
on
andand
keywords with thejoin
keyword. - Align comparison operators (
=
,<
, etc.) for join conditions. - Place the columns of the joined table on the left side of the comparison.
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
select
p.description
,sp.name
from project p
left join sub_project sp
on sp.project_id = p.project_id
- Left align the
union
orunion all
keyword. - Place empty lines before and after the
union
keyword. - Use comments to describe the select statements.
--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
- Place function calls on a single line, unless the line is too long.
- When the line is too long, place the parameters on a new line and indent using 4 spaces
- Left align the closing parentheses
)
with the function name (e.g.datediff
).
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
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
- Consider using
apply
instead of subqueries to improve readability. Usecross apply
for regular (inner) joins andouter apply
for left joins. - Indent subqueries relative to the opening parenthesis or the
apply
keyword, using a multiple of 4 spaces. - Align the closing parentheses
)
with the opening parentheses(
.
💡 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
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
💡 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
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
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
)
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
- Use
in
with constant values only andexists
with subqueries. - Indent subqueries relative to the
exists
keyword, using a multiple of 4 spaces. - Left align the closing parentheses
)
with theexists
keyword.
select p.description
from project p
where exists (
select 1
from sub_project sp
where sp.project_id = p.project_id
)
select p.description
from project p
where p.status in (1, 2, 3) --new, open, closed
- Don't use the
into
keyword. - Always use a column list.
- Place the column list under the
insert
keyword and indent using 4 spaces. - Left align the closing parentheses with the
insert
keyword. - Left align the
select
orvalues
keyword with theinsert
keyword.
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')
)
- Use a from-clause with joins instead of subqueries.
- Always use the alias of the table to update in the
update
statement. - Left align the
set
keyword with theupdate
keyword. - Place the column list after the
set
keyword and indent using 4 spaces. - Align the assignment operators
=
of the column list.
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
- Use a from-clause with joins instead of subqueries.
- Always use the alias of the table in the
delete
statement.
delete sp
from sub_project sp
join project p
on p.project_id = sp.project_id
where p.finished = 1
- Declare all variables at the top of the code template.
- Place the variable list under the
declare
keyword and indent using 4 spaces. - Place commas in front of the variable names.
- Left align the data types for all variables.
declare
@project_id project_id
,@project_vrs_id project_vrs_id
,@tab_id tab_id
- Always use
begin
andend
in an if or while statement. - Left align the
if
,while
,begin
andend
keywords. - Don't use empty lines after the
begin
and before theend
keywords. - Do use empty lines to separate statements within
begin
andend
blocks. - Left align top level
and
keywords with the first condition. - Place
and
keywords in front of the condition. - Place
or
keywords on a separate line, left aligned with the previous line. - Always use parentheses around
or
conditions. - Indent conditions inside parentheses, using a multiple of 4 spaces.
- Align the closing parentheses
)
with the opening parentheses(
. - Align comparison operators (
=
,<
, etc.) for conditions of the same level.
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
if (
(
@project_id = 1
and @project_vrs_id = 'DB'
)
or
@project_status = 3
)
begin
set @project_vrs_id = 'DBA'
end
while @status = 3
and @counter >= 1
begin
set @counter = @counter + 1
end
- Place the column list under the
select
keyword and indent using 4 spaces. - Place commas in front of the column names.
- Left align the data types for all variables.
For table variables, use domains as much as possible.
declare @project table (
project_id project_id
,description description
)
create table #project (
project_id int
,description varchar(200)
)
drop table #project
- Left align the
with
,as
andselect
,update
orinsert
keywords. - Place the column list under the
with
keyword and indent using 4 spaces. - Place commas in front of the column names.
;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
- Place the cursor parameters on the same line as the
declare
keyword. - Left align the
declare
andselect
keywords. - Place all variables on the same line as the
fetch
keyword.
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
- Left align the
begin tran
,commit tran
androllback tran
keywords. - Left align the code within the transaction.
- Don't name the transaction unless there are nested transactions.
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
- Left align the
begin try
,end try
,begin catch
andend catch
keywords. - Indent the code within the try and catch, using a multiple of 4 spaces.
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
- Place the parameters on the same line unless there are many parameters.
- When there are many parameters, place the parameters on a new line and indent using 4 spaces.
- Place commas in front of the parameters.
- Align the
output
keywords.
exec task_kopieer_project @project_id
exec task_kopieer_project
@project_id
,@klant_id
,@datum
,@verwachte_kosten output
,@verwachte_einddatum output
- 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 isCtrl+K,C
andCtrl+K,U
. - Don't describe what code used to do or what has changed.
- Don't leave commented-out code in templates.
/*
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