Tips and tricks for using ApexSQL Refactor SQL formatting options – Parameters, Columns, Joins, and Flow control

Applies to
ApexSQL Refactor

Summary
This article provides a description and an explanation of ApexSQL Refactor T-SQL code formatting options under the Parameters, the Statements, the Joins, and the Flow control tabs.

Description

Parameters

The Parameters sub-tab which is located under the Lists tab provides options for formatting objects definitions, parameters, and alignment in Data Definition Language (DDL) statements:

Example to increase SQL code readability using the Place list on a new line and the Place each item on a new line options:

CREATE PROCEDURE [dbo].[uspSearchCandidateResumes]
    @searchString [nvarchar](1000),   
    @useInflectional [bit]=0,
    @useThesaurus [bit]=0,
    @language[int]=0

Columns

The Columns sub-tab which is located under the List tab provides options for formatting Data Manipulation Language (DML) statements.

The Column lists options provide formatting of the column lists in the DML statements. The Place comma after each item and the Place comma before each item options allow enforcing both SQL formatting standards for formatting commas:

Formatting using the Place comma after each item option:

INSERT #Temp
SELECT DISTINCT
       C.Lastname A ,
       C.Firstname B

Formatting using the Place comma before each item option:

INSERT #Temp
SELECT DISTINCT
       C.Lastname A
     , C.Firstname B

The Statements tab also provides the formatting of aliases and manipulating the AS keyword, as well as the Always use INTO in INSERT statements option:

Before formatting:

INSERT Person.Address(
       Addressline1
     , Addressline2
     , Modifieddate
                     )
VALUES( N'AddressLine1' , 
        N'ddressLine2' , 
        '2014-05-30 23:52:45'
      );

After formatting:

INSERT INTO Person.Address(
            Addressline1
          , Addressline2
          , Modifieddate
                          )
VALUES( N'AddressLine1' , 
        N'ddressLine2' , 
        '2014-05-30 23:52:45'
      );

Quick tip: The INSERT INTO form is the T-SQL standard for the INSERT statement. Although INTO is an optional keyword in the SQL Server INSERT statement, it is mandatory in some other Relational Database Management Systems (e.g. ORACLE) so it is also a good practice to use INTO in INSERT statements and ensure that code is portable.

Joins

The Joins tab provides SQL formatting options for JOIN statements. The set of options include separating joined objects, ON keywords, and nested JOIN statements on a new line, aligning, and indenting code:

Note: In case of the following formatting of the JOIN statement e.g.:

FROM
       Tunes
       INNER JOIN Musicians
       ON Tunes.Name
          = 
          Musicians.Name
       INNER JOIN Duos
       ON Musicians.Name
          = 
          Duos.Name;

If the desired formatting setting is to have the comparing columns in the ON clause in one line check off the Show operations on multiple lines option under the Expressions tab in the Comparison operations set:

Formatting of the JOIN statement after checking off the Show operations on multiple lines option:

FROM
       Tunes
       INNER JOIN Musicians
       ON Tunes.Name = Musicians.Name
       INNER JOIN Duos
       ON Musicians.Name = Duos.Name;

Flow control

The Flow control tab provides formatting options for improving readability in T-SQL condition statements, and wrapping up IF statements and stored procedures in the BEGIN/END block code.

Example using the Condition keywords options:

Before:

SELECT
       productnumber p
     , name n
     , 'Price Range' = CASE
        WHEN listprice = 0 THEN 'Mfg item - not for resale'
        WHEN listprice < 50 THEN 'Under $50'
        ELSE 'Over $1000'

After:

SELECT
       productnumber p
     , name n
     , 'Price Range' = CASE
        WHEN listprice = 0
        THEN 'Mfg item - not for resale'
        WHEN listprice < 50
        THEN 'Under $50'
        ELSE'Over $1000'

Quick tip: To indicate that a statement covers more than one line use the BEGIN/END block in IF statements and stored procedures

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