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, SQL scripts, code in the query window, or a code selection from the query window can be formatted. To set the formatting options open the Options window.

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

The formatting options are available in the Options window:

Formatting SQL options

The Formatting tab in the Options window 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

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:

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.

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: 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, capitalization rules can be set for SQL keywords, data types, identifiers, system functions, and variables using UPPERCASE, lowercase, Proper Case, or leaving the current formatting by deselecting the option:

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, rules can be set for the line (–) and block (/* … */) comments:

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 the Manage empty lines option from the Formatting tab is used, select 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: 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