Rules of SQL formatting – SQL code commenting

In this article, tips on T-SQL code commenting and improving productivity will be given, while using the ApexSQL Refactor’s Comments feature. ApexSQL Refactor is a SQL Server and Visual Studio SQL formatting add-in with nearly 200 formatting options.

The main purpose of comments is to document our code and write descriptions of what code is doing.

Using comments in SQL Server

Microsoft SQL Server supports two types of comments:

  1. (–) is a line comment. It can be used in a same line as SQL code, or on a separate line. This type of comment starts with two dashes and ends with carriage return:

    USE AdventureWorks2012
GO
SELECT * FROM HumanResources.Employee 
SELECT * FROM Person.Address -- Single line comment
GO

    For multiple-line comments every line needs to start with two dashes:

    USE AdventureWorks2012
GO
-- First line of a multiple-line comment
-- Second line of a multiple-line comment
SELECT * FROM HumanResources.Employee 
SELECT * FROM Person.Address -- Single line comment
GO

  2. (/*…*/) is a block comment. It can be used in a same line as SQL code, on a separate line or within code. Everything between the opening and the closing character pair / * is considered a part of a comment.

    Multiple-line /* */ comments cannot span a batch. The complete comment must be contained within a batch. For example, in SQL Query Analyzer and the sql utility, the GO command signals the end of a batch. When the utilities read the characters GO in the first two bytes of a line, they send all the code since the last GO command to the server as one batch. If a GO occurs at the start of a line between the /* and */ delimiters, then an unmatched comment delimiter will be sent with each batch and they will trigger syntax errors. [1]

    USE AdventureWorks2012
GO
/* First line of a multiple-line comment.
   Second line of a multiple-line comment. */
SELECT * FROM HumanResources.Employee 
SELECT * FROM Person.Address /*Single line comment*/
GO

    Block code can be used anywhere within SQL code:

    SELECT BirthDate, /* FirstName, */ MaritalStatus
FROM HumanResources.Employee

    Replacing multiline comment with a single line comment will result with a syntax error:

    SELECT BirthDate, --FirstName, MaritalStatus
FROM HumanResources.Employee 


Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'FROM'

Commenting stored procedures within SQL code

It is considered good practice to start a stored procedure with a comment that gives at least the author, the date, and the update history. After that, add a high-level description of the function of this module. The procedure name will be in a “<verb><object>” format. Each parameter should have a comment as needed.

Example with combination of single line and block comments:

/*
Object: Stored procedure 
Author: Milica Medic
Script Date: December 30, 2013
Description: This procedure gets a list of addresses based 
on the city value that is passed in the @City parameter
*/
CREATE PROCEDURE uspGetAddress @City nvarchar(30)
AS
SELECT * 
FROM AdventureWorks.Person.Address
WHERE City = @City -- the @City parameter value will narrow the search criteria
GO

Examples of commenting parts of stored procedures:

 BEGIN
    SET NOCOUNT ON;
-- Use recursive query to list out all Employees required for a particular Manager
    WITH [EMP_cte]([BusinessEntityID], [OrganizationNode], [FirstName], [LastName],[JobTitle], [RecursionLevel]) -- CTE name and columns
    AS (
        SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName],p.[LastName], e.[JobTitle], 0 -- Get the initial Employee
)

Control Statement Comments

Control statement comments, such as IF-THEN-ELSE, BEGIN-END, and WHILE-DO loops, will look much like comments in any procedural program. Complicated SQL statements may need a comment at the top and often at the clause level:

-- Returns the first name, last name, job title and business entity type for the specified contact.
-- Since a contact can serve multiple roles, more than one row may be returned.
BEGIN
       IF @PersonID ISNOT NULL 
       BEGIN
       IF EXISTS(SELECT * FROM [HumanResources].[Employee] e 
       WHERE e.[BusinessEntityID] = @PersonID) 
       INSERT INTO @retContactInformation
       SELECT @PersonID, p.FirstName, p.LastName, e.[JobTitle], 'Employee'
       FROM [HumanResources].[Employee] AS e
       INNER JOIN [Person].[Person] p
       ON p.[BusinessEntityID] = e.[BusinessEntityID]
       WHERE e.[BusinessEntityID] = @PersonID;
       END
RETURN;
END;

Comments on Clause

Complicated joins may need a comment that explains a series of predicates at a higher level. Also, a derived table without a good alias may need a comment that explains what it contains:

-- Join back to Employee to return the manager name 
   SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[BusinessEntityID],[EMP_cte].[FirstName], [EMP_cte].[LastName],  [EMP_cte].[OrganizationNode].ToString() AS [OrganizationNode], 
p.[FirstName] AS 'ManagerFirstName', 
p.[LastName] AS 'ManagerLastName'  -- Outer select from the CTE
 FROM [EMP_cte] 
 INNER JOIN [HumanResources].[Employee] e
 ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
 INNER JOIN [Person].[Person] p

Comments can be managed by using ApexSQL Refactor. In the Options window, under the Comments tab, there are various options to manipulate comments:

Formatting options dialog in ApexSQL Refactor showing comments manipulation options

To improve readability with adding the empty line before and after block comments using the Insert empty line before block comments option and the Insert empty line after block comments option:

Insert empty line before block comments option and the Insert empty line after block comments option

Before:

USE AdventureWorks2012
GO
/* First line of a multiple-line comment.
   Second line of a multiple-line comment. */
SELECT * FROM HumanResources.Employee 
SELECT * FROM Person.Address -- Single line comment
GO

After:

USE AdventureWorks2012
GO

/* First line of a multiple-line comment.
   Second line of a multiple-line comment. */

SELECT * FROM HumanResources.Employee
SELECT * FROM Person.Address --Single line comment
GO

The block comments can be customized by adding borders using the Add border above block comments using <customized sign> and Add border below block comments using <customized sign> options:

Dialog showing options - Add border above block comments using customized sign and Add border below block comments using customized sign

Before:

USE AdventureWorks2012
GO

/* First line of a multiple-line comment.
   Second line of a multiple-line comment. */

SELECT * FROM HumanResources.Employee
SELECT * FROM Person.Address /*Single line comment*/
GO

After:

USE AdventureWorks2012
GO

/******************************************
 First line of a multiple-line comment.
   Second line of a multiple-line comment. 
******************************************/

SELECT * FROM HumanResources.Employee
SELECT * FROM Person.Address

/******************
Single line comment
******************/
GO

ApexSQL Refactor offers an option to change the type of existing comments depending on the user needs. If all of the single line comments need to be changed into block comments, use the Change all comments to block comments option:

Before:

USE AdventureWorks2012
GO
-- First line of a multiple-line comment.
-- Second line of a multiple-line comment. 

SELECT * FROM HumanResources.Employee
SELECT * FROM Person.Address
 
--Single line comment

GO

After:

USE AdventureWorks2012
GO

/******************************************
 First line of a multiple-line comment.
   Second line of a multiple-line comment. 
******************************************/

SELECT * FROM HumanResources.Employee
SELECT * FROM Person.Address

/******************
Single line comment
******************/
GO

All block comments can be changed into line comments using the Change all comments to line comments option:

Dialog showing Change all comments to line comments option

Before:

USE AdventureWorks2012

GO
/****************************************
 First line of a multiple-line comment.
 Second line of a multiple-line comment. 
****************************************/

SELECT * FROM HumanResources.Employee

SELECT * FROM Person.Address

/******************
Single line comment
******************/

GO

After:

USE AdventureWorks2012
GO
-- First line of a multiple-line comment.
-- Second line of a multiple-line comment. 

SELECT * FROM HumanResources.Employee
SELECT * FROM Person.Address
 
--Single line comment

GO

To save time deleting comments, or if there are some unnecessary comments, use the Remove all block comments option and the Remove all single line comments option:

Dialog showing the Remove all block comments option and the Remove all single line comments option

Before:

USE AdventureWorks2012

GO
/****************************************
 First line of a multiple-line comment.
 Second line of a multiple-line comment. 
****************************************/

SELECT * FROM HumanResources.Employee

SELECT * FROM Person.Address

/******************
Single line comment
******************/

GO

After:

USE AdventureWorks2012

GO
SELECT * FROM HumanResources.Employee
SELECT * FROM Person.Address
GO

To remove all single line comments, use the Remove all single line comments option:

Dialog showing the Remove all single line comments option

Before:

USE AdventureWorks2012
GO
-- First line of a multiple-line comment.
-- Second line of a multiple-line comment. 
SELECT * FROM HumanResources.Employee
SELECT * FROM Person.Address -- Single line comment
GO

After:

USE AdventureWorks2012
GO
SELECT * FROM HumanResources.Employee
SELECT * FROM Person.Address
GO

References:
[1] Books Online for SQL Server – Using Comments

Useful resources:
Books Online for SQL Server – /*…*/ (Comment) (Transact-SQL)
Books Online for SQL Server – — (Comment) (Transact-SQL)
Books Online for SQL Server – Comments

 

Related posts:

  1. How to quickly convert SQL code to language-specific client code
  2. SQL code refactoring – ways to improve database performance (part II) – Split table
  3. How to format SQL like a pro – formatting to implicit Microsoft standards and guidance
  4. How to rename database objects in a SQL Server safely – Part 2
  5. How to refactor a SQL Server database table to add a surrogate key
February 12, 2014