SQL Formatting standards – Capitalization, Indentation, Comments, Parenthesis

Nobody likes to read a wall of text, even when it’s just plain text. When it comes to reading code, the problem is even bigger. Code can have different formatting styles, which could make your job either easier or more difficult. It can make code difficult to decipher and understand. A clean and neat SQL is read faster than an inconsistently written SQL; SQL reviewing and troubleshooting is more efficient; joint development efforts are more effective; handing off projects from one team to another is smoother.

How to make your SQL readable so that it is enough just to skim through the code and get the general idea

It’s the SQL formatting that makes the difference.

The best solution is to create a personalized style guide for SQL. The problem, but at the same time the advantage you can utilize, is that there are neither style nor formatting standards, and it’s all a matter of preference. There’s no “best” SQL formatting style, so it’s up to you and your team to create your own set of formatting rules that will be enforced to all SQL you work on. The best you can do for your SQL readability is to have the whole team always enforce the set standard – that way, everyone will be familiar with the formatting used in the company, and will be able to quickly read the code Here are some basic guidelines you can use when setting the SQL formatting rules:

  • Use comments to describe what SQL does. If you’re modifying existing SQL, add the author’s name, the date, describe the modifications, and avoid questions. Don’t overdo it and comment on the obvious
  • Put each major SQL statement on a new line

    USE Adventureworks2012;
    
    SELECT
             S.Businessentityid,
             E.Jobtitle
    FROM Sales.Salesperson
    WHERE S.Businessentityid > 10

  • Put SQL keywords such as SELECT and FROM, built-in function names such as SUM, AVG, CASE, and data types such as INT, CHAR, NTEXT in the uppercase:

    CREATE TABLE Sales.Customer(
            Customerid INT IDENTITY(1, 1) NOT NULL,
            Personid INT NULL,
            Storeid INT NULL,
            Territoryid INT NULL,
            Modifieddate DATETIME NOT NULL,
            CONSTRAINT Pk_Customer_Customerid PRIMARY KEY 
            CLUSTERED(Customerid ASC)
                WITH(PAD_INDEX = OFF, 
                STATISTICS_NORECOMPUTE = OFF)ON [Primary])
    
    ON [Primary]

  • Use CamelCase capitalization and do not separate name parts with underscores: TableName, instead of Table_name, or use lower case and underscores to separate name parts: table_name
  • Set standard abbreviations for frequently used objects, such as tbl for tables, or sp for stored procedures
  • Use single quotation for characters, strings, binary and Unicode –
  • Set a rule for naming aliases
  • Use indenting to align wrapped long lines
  • Use parentheses in complex mathematical expressions
  • Be consistent with indentation – use either tab or space
  • Don’t avoid using line breaks to improve readability
  • Code Grouping – keep the lines that execute a certain task in separate code blocks
  • Limit line length – wrap the lines longer than approximately 80 characters

And here is what you should avoid doing:

  • Deeply nest the statements
  • Use ambiguous names – be consistent, use simple and clear naming. Avoid excessive abbreviations
  • Write a flow of execution which is difficult to follow

Once the rules are set, you need to find an easy way to enforce them. Manually applying all the rules is a time-consuming process where mistakes are very likely to happen.

Use our SQL code formatter as a solution here

ApexSQL Refactor is a SQL Server Management Studio and Visual Studio add-in which formats and refactors SQL utilizing over 160 formatting options. It can be used to distribute and enforce SQL formatting rules among team members.

  1. In SQL Server Management Studio or Visual Studio’s Main menu, click ApexSQL Refactor
  2. From the ApexSQL Refactor menu, select Options

    ApexSQL Refactor SSMS menu

  3. Set the specific option as described in the article below
  4. Preview the option’s effect on the current query or a built-in example

    ApexSQL Refactor formatting options for columns

  5. In SQL Server Management Studio or Visual Studio’s Main menu, select ApexSQL, and choose the ApexSQL Refactor. Click the Format SQL option, and apply the default or custom formatting to SQL code in the current Query window

Capitalization

Before setting the capitalization standards, make sure that your database doesn’t have case-sensitive collation, as this can cause big problems when playing with capitalization. You should set the rules for:

  • Reserved/key words (e.g. SELECT, DECLARE, CREATE, ALTER). Upper case is recommended
  • Data types (int, nvarchar, varchar)
  • Object names – identifiers (table, view and stored procedure names)
  • System and built-in functions (SUBSTRING, ABS, LEFT)
  • Variables

Keyword caplitalization settings

Style 1 – Keywords, types and identifiers in lowercase

create table humanresources.department(
             departmentid smallint identity(1, 1) not null,
             name dbo.Name not null,
             groupname dbo.Name not null,
             modifieddate datetime not null,
             constraint pk_department_departmentid 
primary key clustered(departmentid asc)
                 with(pad_index = off, allow_page_locks = on)on [primary])

on [primary];

Style 2 – Keywords in upper case, types in lowercase, identifiers in proper case

CREATE TABLE Humanresources.Department(
             Departmentid smallint IDENTITY(1, 1) NOT NULL,
             Name dbo.Name NOT NULL,
             Groupname dbo.Name NOT NULL,
             Modifieddate datetime NOT NULL,
             CONSTRAINT Pk_Department_Departmentid 
PRIMARY KEY CLUSTERED(Departmentid ASC)
                 WITH(PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON)ON [Primary])

ON [Primary];

Indentation

Indenting makes SQL easier to follow, as it makes it visually structured

It’s recommended not to indent the first line in a multiple line statement, so it would be clear where the statement starts. Make sure that the SQL left margin is indented per the section nesting

Be consistent with indenting – set up several spaces to use for a tab, or always use the same number of spaces. Using tabs instead of spaces is easier, as it requires less clicks, but when you start cutting and pasting your SQL, spaces are easier to handle

First, set whether to use the space or tab for indenting

SQL indentation options

ApexSQL Refactor provides multiple options for indenting arithmetic, logical and comparison operations, schema statements, data statements, and column lists. Check out the Expressions, Statements, Joins, Lists, Flow control and Obfuscation tabs to see all available options

Formatting options for expressions

Style 1 – each clause begins on a new line, none is indented

select s.businessentityid, e.jobtitle
from sales.salesperson as s
inner join humanresources.employee as e on e.businessentityid = 
s.businessentityid

Style 2 – each clause and each set of arguments begins on a separate line. Each statement subordinate to SELECT is also indented

SELECT
         S.Businessentityid, E.Jobtitle
  FROM
         Sales.Salesperson
        AS S
         INNER JOIN
         Humanresources.Employee
                       AS E
              ON E.Businessentityid = S.Businessentityid;

Style 3 – indent all list items equally, together with all columns and table names

SELECT
         S.Businessentityid,
         E.Jobtitle,
         S.Salesquota,
         S.Salesytd,
         S.Saleslastyear
  FROM
         Sales.Salesperson
         AS S
         INNER JOIN
         Humanresources.Employee
         AS E
         ON E.Businessentityid = S.Businessentityid;

Parentheses

Parentheses can be used in different contexts – in SELECT statements, function parameters, DDL statements, mathematical expressions, etc.

The opening parenthesis in an expression can be placed on a new line and followed by an immediate line break. The closing parenthesis can also be placed on a new line, with or without a line break afterwards. Use whatever makes your code more readable:

General formatting options

Style1 – no line breaks after opening or closing parentheses

CREATE TABLE Humanresources.Department (
        Departmentid SMALLINT IDENTITY ( 1, 1 )
                                   NOT NULL,
        Name dbo.Name NOT NULL,
        Groupname dbo.Name NOT NULL,
        Modifieddate DATETIME NOT NULL,
        CONSTRAINT Pk_Department_Departmentid 
        PRIMARY KEY CLUSTERED ( Departmentid ASC )
           WITH ( PAD_INDEX = OFF, 
           STATISTICS_NORECOMPUTE = OFF, 
           IGNORE_DUP_KEY = OFF, 
           ALLOW_ROW_LOCKS = ON, 
           ALLOW_PAGE_LOCKS = ON ) ON [Primary] )

ON [Primary];

Style2 – a line break after the opening parenthesis

CREATE TABLE Humanresources.Department (
            Departmentid SMALLINT IDENTITY ( 1, 1 )
                                       NOT NULL,
            Name dbo.Name NOT NULL,
            Groupname dbo.Name NOT NULL,
            Modifieddate DATETIME NOT NULL,
            CONSTRAINT Pk_Department_Departmentid 
            PRIMARY KEY CLUSTERED ( Departmentid ASC )
               WITH ( PAD_INDEX = OFF, 
               STATISTICS_NORECOMPUTE = OFF, 
               IGNORE_DUP_KEY = OFF, 
               ALLOW_ROW_LOCKS = ON, 
               ALLOW_PAGE_LOCKS = ON ) 

    ON [Primary];

Style3 – line breaks after both opening and closing parentheses

CREATE TABLE Humanresources.Department
(
      Departmentid SMALLINT IDENTITY 
      ( 
            1, 1
      ) NOT NULL,
      Name dbo.Name NOT NULL,
      Groupname dbo.Name NOT NULL,
      Modifieddate DATETIME NOT NULL,
      CONSTRAINT Pk_Department_Departmentid 
      PRIMARY KEY CLUSTERED 
      ( 
            Departmentid ASC 
      )
      WITH 
      ( 
            PAD_INDEX = OFF, 
            STATISTICS_NORECOMPUTE = OFF, 
            IGNORE_DUP_KEY = OFF, 
            ALLOW_ROW_LOCKS = ON, 
            ALLOW_PAGE_LOCKS = ON
      )
) ON [Primary]

Comments

Comment types that can be used in SQL are:

  • Block comments /*…*/
  • Inline comments —

Here are a couple of guidelines for comments you should follow:

  • Write comments clearly and consistently
  • Don’t add too many comments
  • Check whether or not you really need a developer’s name and revision history

ApexSQL Refactor comments formatting options

-- Select top sales agents
SELECT
         S.Businessentityid,
         E.Jobtitle,
         S.Salesquota,
         S.Salesytd,
         S.Saleslastyear
  FROM Sales.Salesperson
  WHERE S.Businessentityid > 10
  ORDER BY
         S.Salesquota;
/*add them to the bonus table*/

Using ApexSQL Refactor, one type of comment can be changed to another one

/* Select top sales agents*/
SELECT
      S.Businessentityid,
      E.Jobtitle,
      S.Salesquota,
      S.Salesytd,
      S.Saleslastyear
FROM Sales.Salesperson
WHERE S.Businessentityid > 10
ORDER BY
      S.Salesquota;
/*add them to the bonus table*/ 

Creating a SQL formatting standard usually takes a lot of testing and tweaking. Once you are satisfied with the rules, save them to a formatting profile and distribute to all team members

  1. In the Options dialog, click Export to save the formatting rules to an XML file

    ApexSQL Refactor general formatting options

  2. Specify the file name and location
  3. Copy the XML file to a team mate’s machine
  4. In the Formatting options dialog, click Import
  5. Navigate to the XML file

Now your colleagues will have the same formatting as you do

With SQL, its readability is as important as the fact that it can be executed. Clear formatting guidelines and automatic implementation of formatting rules will ensure that all team members and anyone who inherits the code can read it easily. Use ApexSQL Refactor to automatically implement all SQL formatting rules

April 4, 2013
  • Andreas

    Great tool, but how do I control the indentation of comments? Tried, but the comment is still at the beginning of the line and not inline with the following code.

    Cheers

    https://uploads.disquscdn.com/images/db8e613b357528449c9733c8e1cd09109332fef0dd95e92ab80f6fbc2bf28fe7.png

    • Marko

      Hi Andrea,

      For the next release of ApexSQL Refactor, we plan to improve
      the control of comments in SQL scripts by adding new options under the Comments
      tab of the Options dialog. Next release of ApexSQL Refactor will be in
      about two months.