SQL query basics – How to improve readability by formatting commas, spacing and alignment

The previous article covered SQL query readability basics such as capitalization strategies and their implementation in SQL formatter by ApexSQL. This time, commas, spacing, and aligning will be detailed. One of the quickest ways to wreak havoc among developers is to start a discussion about how commas should be treated within the code, particularly in a SELECT list. Let’s look at how commas can be treated in ApexSQL Refactor. The following SELECT statement includes two series that require commas: a SELECT list and an ORDER BY clause:

SELECT FirstName,

       MiddleName,

       LastName,

       City,

       StateProvinceName

FROM HumanResources.vEmployee

WHERE JobTitle LIKE 'Support Engineer%'

ORDER BY StateProvinceName, City

In the SELECT list the commas are placed at the end of the column names. However, in the ORDER BY clause, the column names are on one line, so the comma is placed after the first column name, followed by a space. Another approach is to precede the column names in the SELECT list with a comma, as in the following example:

SELECT FirstName

      ,MiddleName

      ,LastName

      ,City

      ,StateProvinceName

FROM HumanResources.vEmployee

WHERE JobTitle LIKE 'Support Engineer%'

ORDER BY StateProvinceName, City

In this example, the comma was placed directly in front of the column names in the SELECT list. However, you can also choose to add a fixed number of spaces after each column:

SELECT FirstName

,   MiddleName

,   LastName

,   City

,   StateProvinceName

FROM HumanResources.vEmployee

WHERE JobTitle LIKE 'Support Engineer%'

ORDER BY StateProvinceName, City

As you can see, there are several approaches you can take, and comma usage is by no means limited to SELECT lists and ORDER BY clauses. In the following DDL statements, commas are used to separate the elements of the OBJECT_ID function and the column definitions:

IF OBJECT_ID

   (

 'ApexDocs',

 'U'

   ) IS NOT NULL

DROP TABLE ApexDocs

GO

CREATE TABLE ApexDocs

(

  DocID int NOT NULL IDENTITY,

  DocTitle nvarchar(50) NOT NULL,

  DocFileName nvarchar(400) NOT NULL,

 CONSTRAINT PK_ApexDocs_DocID PRIMARY KEY CLUSTERED (DocID ASC)

)

GO

In this case, the function parameters are treated like the column definitions. However, whatever strategy you implement, you must take into account various ways in which commas are used. Some part of those considerations will depend on how you space and align various elements in your statements.

Everyone has an opinion about how code should be spaced, indented, and broken across lines. In fact, trying to determine the spacing and alignment of the various SQL elements might prove to be a bit challenging, but the more consistent the code across the organization, the better.

Let’s look at a few examples that show different spacing strategies in ApexSQL Refactor. In the first example, each clause begins on a separate line:

SELECT FirstName, MiddleName, LastName, City, StateProvinceName

FROM HumanResources.vEmployee

WHERE JobTitle LIKE 'Support Engineer%'

ORDER BY StateProvinceName, City

Comparing this example with the following one, each clause and each clause set argument begins on separate lines:

SELECT

   FirstName, MiddleName, LastName, City, StateProvinceName

FROM

   HumanResources.vEmployee

WHERE

   JobTitle LIKE 'Support Engineer%'

ORDER BY

   StateProvinceName, City

In both examples, SQL code can be easily read because we have used very simple statements. However, the more complex SQL statements are, the more important it is to have SQL formatting standards, so they can deal with these complexities. For instance, if clauses cannot fit into a single line, you need to determine how to handle line wraps such as the SELECT list in the following example:

SELECT FirstName, MiddleName, LastName,

   City, StateProvinceName

FROM HumanResources.vEmployee

WHERE JobTitle LIKE 'Support Engineer%'

ORDER BY StateProvinceName, City

Notice that the SELECT list now spans two lines and that the second line is indented. However, instead of taking this approach you might separate the elements in the SELECT list and ORDER BY clause onto separate lines:

SELECT

   FirstName,

   MiddleName,

   LastName,

   City,

   StateProvinceName

FROM

   HumanResources.vEmployee

WHERE

   JobTitle LIKE 'Support Engineer%'

ORDER BY

   StateProvinceName,

   City

Clear SQL formatting guidelines and automatic implementation of formatting rules will ensure that all team members and anyone who inherits the code can easily read it. Stay tuned for the next article, which will cover SQL query readability basics: aliases and column lists

Useful resources:

Manage code formatting
How to capitalize one or more words
Convert any uppercase sentence as normal sentence