SQL formatting guidelines

Introduction

Readable code helps you and your colleagues. It makes it easier to understand, transfer, and share your work.

You can deliver readable code by sharing formatting guidelines as a team. Adhere to your shared formatting guidelines to make your team's code more consistent.

On this page, you can find a basic set of SQL formatting guidelines that can serve as a starting point for your team. These guidelines also provide examples of what your properly formatted code might look like.

Basics

Your code should be readable and easy to understand. Other people should be able to grasp what your code is doing by reading your comments. The structure of your code should be easy to follow.

Thinkwise advises to use the Redgate SQL Prompt tool to help you format your SQL code for Thinkwise projects.

How to install Redgate SQL Prompt in Azure SQL Data Studio

1. Search for Redgate SQL prompt in the extensions of Azure SQL Data studio.
2. Download the Redgate SQL prompt VSIX file from the extension page.
3. Go to File > Install Extension from VSIX Package and install the package.

How add the Thinkwise style to Redgate SQL Prompt

1. Press Ctrl + Shift + P.
2. Search for SQL Prompt: Create Formatting Style and open it.
3. Select one of the default styles to copy.
4. Name your style (for example, Thinkwise).
5. Paste the code of this repository's thinkwise.formattingstyle file into your created file.
6. Save the file.

How to choose your active formatting style

1. Press Ctrl + Shift + P.
2. Search for SQL Prompt: Change Active Formatting Style.
3. Choose your desired formatting style.

How to use Redgate SQL Prompt as the default formatter

note

If this is your first formatter, this step is probably not necessary.

To use Redgate SQL Prompt as the default formatter (using Shift + Alt + F), follow these steps:

1. Right-click on a document.
2. Select Format Document With.
3. Choose Redgate SQL Prompt.

Guidelines in JSON format

Guidelines

{
  "metadata": {
    "id": "97cd959c-08c1-4510-9a39-99bf6b606fac",
    "name": "Thinkwise Software"
  },
  "whitespace": {
    "newLines": {
      "preserveExistingEmptyLinesAfterBatchSeparator": false
    }
  },
  "lists": {
    "alignItemsAcrossClauses": false,
    "alignAliases": true,
    "placeCommasBeforeItems": true,
    "addSpaceAfterComma": false
  },
  "parentheses": {
    "collapseShortParenthesisContents": true,
    "collapseParenthesesShorterThan": 35
  },
  "casing": {
    "reservedKeywords": "lowercase",
    "builtInFunctions": "lowercase",
    "builtInDataTypes": "lowercase",
    "globalVariables": "lowercase",
    "useObjectDefinitionCase": true
  },
  "dml": {
    "collapseShortStatements": true,
    "collapseStatementsShorterThan": 35,
    "collapseShortSubqueries": true,
    "collapseSubqueriesShorterThan": 78
  },
  "ddl": {
    "alignDataTypesAndConstraints": true,
    "placeFirstProcedureParameterOnNewLine": "never",
    "collapseShortStatements": true,
    "collapseStatementsShorterThan": 55
  },
  "controlFlow": {
    "collapseStatementsShorterThan": 78
  },
  "cte": {
    "placeColumnsOnNewLine": true,
    "columnAlignment": "indented"
  },
  "variables": {
    "placeAssignedValueOnNewLineIfLongerThanMaxLineLength": false
  },
  "joinStatements": {
    "join": {
      "keywordAlignment": "toTable",
      "indentJoinTable": false
    },
    "on": {
      "keywordAlignment": "rightAlignedToInner"
    }
  },
  "insertStatements": {
    "columns": {
      "parenthesisStyle": "compactSimple",
      "indentContents": false
    },
    "values": {
      "parenthesisStyle": "expandedSimple",
      "placeSubsequentValuesOnNewLines": "always"
    }
  },
  "caseExpressions": {
    "placeExpressionOnNewLine": false,
    "whenAlignment": "toFirstItem",
    "collapseCaseExpressionsShorterThan": 55
  },
  "operators": {
    "comparison": {
      "align": true
    },
    "andOr": {
      "alignment": "rightAligned"
    },
    "between": {
      "placeOnNewLine": false
    },
    "in": {
      "placeFirstValueOnNewLine": "never"
    }
  }
}

Do's

• Explain each block of code using comments.
• Put a comment after each domain or literal that you use.
• Indent code blocks.
• Use spaces, not tabs, for indentation.
• Use commas in front of column names.
• Use begin/end after stating a condition.
• Use column lists for inserts.
• Align opening and closing brackets on the same column.
• Include aliases for calculated values or literals.
• Use lower case only.

Don'ts

• Use reserved keywords.
• Leave commented development items in your code, like variable assignments or helper queries.

Examples

Basic alignment

select si.sales_invoice_id
      ,si.invoice_date
      ,si.due_date 
from sales_invoice si
where sales_invoice_status = 'o' --open

Alias alignment and use of functions

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

Join alignment

select si.sales_invoice_id
      ,si.due_date 
      ,sil.amount
      ,so.sales_order_number
from sales_invoice si
     join sales_invoice_line sil
       on sil.invoice_id = si.invoice_id
     left join sales_order so
       on so.sales_order_id = sil.sales_order_id

If statement

--Notify the user when the amount open on the invoice is greater than 0 and the due date lies in the past. 
if exists (select *
           from invoice i
           where i.invoice_id  = @invoice_id
             and i.amount_open > 0
             and i.due_date    >= getutcdate()
          )
begin
    exec tsf_send_message amount_open_greater_than_zero, null, 0
end

Case statement: simple case

select case si.sales_type
            when 1 then 1 --1 = internal, 1 = partner
            when 2 then 3 --2 = external, 3 = customer
            else        0 --0 = other
       end as sales_category
from sales_invoice si

Case statement: search case

select case when sol.sales_price > sol.max_price --when sales price is greater than the max price. 
                 then 1
                 else 0
       end as exceeds_max_price
from sales_order_line sol       

More complex select query

with wolz (work_order_line_id
          ,branch_subsidiary
          )
as (select wol.work_order_line_id                as work_order_line_id
          ,wol.amount_of_items * processing_time as time_required
    from work_order_line wol
   )
select wol.call_description          as call_description
from work_order_line wol
     join wolz w
       on w.work_order_line_id = wol.work_order_line_id
where wol.work_order_category_id  = @work_order_category_id
  and (select sum(h.number_of_hours) as number_of_hours
       from hour h
       where h.project_id = p.project_id
      ) <= 100
group by wol.subsidiary_id
        ,w.time_required
having sum(wol.work_order_id) = 1
   and count(*)               = 3
order by wol.branch_id
        ,w.time_required

Comparison alignment

select wo.subsidiary_id as subsidiary_id
      ,wo.branch_id     as branch_id
      ,wo.work_order_id as work_order_id
from work_order wo
     join work_order_line wol
       on wol.work_order_id = wo.work_order_id
      and wol.subsidiary_id = wo.subsidiary_id
      and wol.branch_id     = wo.branch_id
     join work_order_line_part wolp
       on wolp.subsidiary_id      = wol.subsidiary_id
      and wolp.branch_id          = wol.branch_id
      and wolp.work_order_id      = wol.work_order_id
      and wolp.work_order_line_id = wol.work_order_line_id
where wo.work_order_category_id  = @work_order_category_id
  and wol.amount                 >= 40
  and (   wol.clearance         = 1 --true
       or wo.customer_reference = 4 --ref_x
      )

Variables

declare @contact_person_id               person_id
       ,@contact_organisation_id         organisation_id 
       ,@is_default_contact_person       bit = 0 --default false
       ,@is_default_contact_organisation bit = 0 --default false 

select @contact_person_id       = o.person_id
      ,@contact_organisation_id = o.organisation_id 
from organisation o

if @contact_person_id is not null 
begin
    set @is_default_contact_person_id       = 1 
    set @is_default_contact_organisation_id = 1
end

Insert based on variables

insert into task(person_id
                ,dt.default_task_id
                ,task_name
                ,task_description
                ,added_by
                ,added_on
                )
values (@person_id
       ,@default_task_id
       ,@task_name
       ,@task_description
       ,dbo.tsf_user()
       ,getutcdate()
       )                

Insert based on select statement

insert into task(person_id
                ,default_task_id
                ,task_name
                ,task_description
                ,added_by
                ,added_on
                )
select p.person_id
      ,dt.default_task_id 
      ,dt.task_name
      ,dt.task_description
      ,dbo.tsf_user()
      ,getutcdate()
from person p
     join person_default_task pdt
       on pdt.person_id = p.person_id
     join default_task dt
       on dt.default_task_id = pdt.default_task_id
where p.person_id = @person_id
  and dt.active   = 1 --only active default tasks. 

Merge statement

merge into task trgt
using (select p.person_id
             ,dt.default_task_id
             ,dt.task_name
             ,dt.task_description
             ,dbo.tsf_user()      as added_modified_by
             ,getutcdate()        as added_modified_on
       from person p
            join person_default_task pdt
              on pdt.person_id = p.person_id
            join default_task dt
              on dt.default_task_id = pdt.default_task_id
       where p.person_id = @person_id
         and dt.active   = 1 --only active default tasks. 
      )src
on (    src.person_id       = trgt.person_id
    and src.default_task_id = trgt.default_task_id
   )      
when matched 
then update 
set task_name       = src.task_name
   ,task_desciption = src.task_description
   ,modified_by     = added_modified_by
   ,modified_on     = added_modified_on
when not matched
then insert (person_id
            ,default_task_id
            ,task_name
            ,task_description
            ,added_by
            ,added_on
            )
values(src.person_id
      ,src.default_task_id
      ,src.task_name
      ,src.task_description
      ,src.added_modified_by
      ,src.added_modified_on
      );