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 a 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 SQL code 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 that can be utilized, 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 user and team to create set of formatting rules that will be enforced to all SQL code that is worked on. The best option that can be done for 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 that can be used when setting the SQL formatting rules:

• Use comments to describe what SQL does. If existing SQL code is modified, 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 should be avoided:

• 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, 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 200 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 menu
   
2. From the ApexSQL Refactor menu, click the Options command to initiate the Options window:
   

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

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

Capitalization

Before setting the capitalization standards, make sure that a database doesn’t have case-sensitive collation, as this can cause big problems when playing with capitalization. The rules should be set 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
  

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 cutting and pasting of SQL code is started, spaces are easier to handle.

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

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:

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::

Style 1 – 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];

Style 2 – 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]);

Style 3 – 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 that should be followed:

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

-- 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 the rules are satisfying, save them to a formatting profile and distribute to all team members:

1. In the Options window, click the Export button to save the formatting rules to an XML file:
   

1. Specify the file name and location
2. Copy the XML file to a team mate’s machine
3. In the Options window, click the Import button
4. Navigate to the XML file

Now all colleagues will have the same formatting.

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