Tips and tricks for using ApexSQL Refactor SQL formatting options – General options, Capitalization and Comments

Applies to
ApexSQL Refactor

Summary
This article provides a description and an explanation of ApexSQL Refactor T-SQL code formatting options in the Formatting, the Capitalization, and the Comments tab.

Description

Setting up the SQL formatting options

Using ApexSQL Refactor, you can format SQL scripts, code in the query window, or a code selection from the query window. To set the formatting options open the Options dialog.

From the ApexSQL menu, under the ApexSQL Refactor menu, select the Options command:

Selecting Formatting options in the ApexSQL Refactor sub-menu

The formatting options are available in the Options dialog:

Figure showing the Formatting options dialog

Formatting SQL options

The Formatting tab in the Options dialog contains settings for Indention, Wrapping, Spacing, Empty lines, and Miscellaneous.

The Indention options set offers three different styles of code indenting. Selecting the Indent using spaces option or the Indent using tabs option, specify whether to use tabs or space characters when indenting. The Smart indent option aligns identifiers in the data statements like SELECT, INSERT, UPDATE, DELETE

Figure illustrating the Indention options in ApexSQL Refactor

Quick tip icon

Quick tip:

If the Indent using tabs option is selected, use the Tabs equal X spaces option to specify how many spaces compose a single indentation or tab

The Wrapping option wraps lines longer than X characters:

Figure showing the Wrapping option in ApexSQL Refactor

Quick tip icon

Quick tip:

Use this option to insert a line break before the first word that goes beyond the specified number of characters and avoid the need for horizontal scrolling in long scripts

The Always use statement terminator option, under the Miscellaneous options, terminates statements with a semicolon.

The Miscellaneous options in ApexSQL Refactor

Terminating SQL statements with a semicolon is mandatory for the following statements:

  1. Terminating a statement that precedes the Service Broker Statements (SEND, RECEIVE, BEGIN DIALOG CONVERSATION, and BEGIN CONVERSATION TIMER) if those statements are not first in the batch.
  2. Terminating a statement that precedes the Common Table Expression (CTE) if the WITH if statements is not first in the batch.
  3. Terminating the statement that precedes the THROW statement in the CATCH block if the THROW statement is not the first statement in the CATCH block.
  4. Terminating the MERGE statement.
Quick tip icon

Quick tip:

Not ending T-SQL statements with a semicolon is a deprecated feature in SQL Server and it will became mandatory in a future version

Capitalization

Under the Capitalization tab you can set capitalization rules for SQL keywords, data types, identifiers, system functions, and variables using UPPERCASE, lowercase, Proper Case, or leaving the current formatting by deselecting the option:

Using Capitalization tab to set capitalization rules

Quick tip icon

Quick tip:

Majority of the capitalization standards suggest using uppercase letters for keywords and system functions, proper case for identifiers, and lowercase for variables and data types

An example using all lowercase letters:

create procedure materials 
@Start int , 
@Check datetime
with encryption
as
begin
    set nocount on;

An example using standardized formatting:

CREATE PROCEDURE Materials 
@Start int , 
@Check datetime
WITH ENCRYPTION
AS
BEGIN
    SET NOCOUNT ON;

Comments

ApexSQL Refactor supports both types of Microsoft® SQL Server™ comments. Under the Comments tab you can set rules for the line (–) and block (/* … */) comments:

Figure illustrating the Comments tab in ApexSQL Refactor

Quick tip icon

Quick tip:

To increase SQL code readability, use options for inserting empty lines before and after block comments

Before:

USE AdventureWorks2012; 
GO
/* Multiple line comment.
Multiple line comment. */
SELECT *
FROM HumanResources.Employee; 
SELECT *
FROM Person.Address; /* In line comment */
GO

After:

USE AdventureWorks2012; 
GO

/* Multiple line comment.
Multiple line comment. */

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

Note: To keep formatting of empty lines before and after block comments in case you use the Manage empty lines option from the Formatting tab, select the Keep empty lines before/after comments option:

Selecting the Keep empty lines before/after comments option

The Enforce comment type option enforces specified comment type to all comments in the script. For example the Change all comments to block comment option enforces all block comments in the SQL script:

Before:

USE AdventureWorks2012; 
GO

--Multiple line comment. 
--Multiple line comment.

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

After:

USE AdventureWorks2012; 
GO

/* Multiple line comment. 
Multiple line comment. */

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

Quick tip icon

Quick tip:

In case of the block (/* */) comments if a GO statement occurs at the start of a line between the /* and */ delimiters an unmatched comment delimiter will be sent with each batch and trigger syntax errors

Useful resources:
Manage Code Formatting
SQL Server Best Practices
Deprecated Database Engine Features in SQL Server 2014