ApexSQL Refactor vs RedGate® SQL Prompt Formatting – Part 3 – Common formatting options compared

Previously, common database refactors supported by ApexSQL Refactor and RedGate® SQL Prompt were compared in ApexSQL Refactor vs RedGate® SQL Prompt Refactors – Part 1 – Common refactors compared “Encapsulate As”, as well as additional refactors not supported by SQL Prompt in ApexSQL Refactor vs RedGate® SQL Prompt – Part 2 – Additional refactors

Standardizing T-SQL code across an organization is essential for effective code reviews and making troubleshooting T-SQL script easier. In this article, formatting options in ApexSQL Refactor and RedGate® SQL Prompt will be compared.

RedGate® SQL Prompt – Capitalization options

RedGate® SQL Prompt offers three options for capitalization: lowercase, UPPERCASE and Leave as is.

These options are available for reserved keywords, built-in functions, and built-in data types.

ApexSQL Refactor – Capitalization options

In addition to lower case and UPPER CASE options, ApexSQL Refactor has the Proper Case capitalization option, a common convention to only use capital letters to start principal words.

Beside keywords and built-in functions, these options are available for keywords, identifiers, data types, system functions, and variables:

ApexSQL Refactor supports capitalization of not just built- in datatypes, but also user defined datatypes. For example, user defined data type name can be formatted the same way as any other built–in datatype.

CREATE TABLE testcaps(Col_1 NAME)

After formatting:

CREATE TABLE testcaps(
        Col_1 NAME)

This option is not supported by SQL Prompt.

RedGate® SQL Prompt – Data statements formatting options

SQL Data Statements, or DML (Data Manipulation Language) performs a query and modification on database tables and columns

RedGate® SQL Prompt has the options of placing first column, contents of parentheses, join conditions, closing parenthesis, indent sub clauses, and each subsequent column, placing derived tables and sub queries on a single line and aligning JOIN statement with the FROM statement.

From this code:

SELECT TOP 100 p.productid , 
               p.name , 
               p.listprice , 
               p.size , 
               p.modifieddate , 
      sod.unitprice ,sod.unitpricediscount FROM  sales.salesorderdetail AS sod 
      INNER JOIN production.product AS p ON sod.productid = p.productid WHERE 
sod.unitprice > 1000;

After formatting, it will look like this:

SELECT TOP 100 
    p.productid , 
    p.name , 
    p.listprice , 
    p.size , 
    p.modifieddate , 
    sod.unitprice ,sod.unitpricediscount 
FROM 
    sales.salesorderdetail AS sod 
    INNER JOIN production.product AS p ON sod.productid = p.productid 
WHERE 
    sod.unitprice > 1000;

ApexSQL Refactor – Data statements formatting options

ApexSQL Refactor offers separate formatting options for all DML statements. Column lists can be placed on a new line, aligned with a keyword or indented by a custom number of spaces:

The Data statements formatting option includes aligning aliases, the choice whether to always use AS for aliases in SELECT statements, and placing AS on a new line. Other options include formatting to always use INTO in INSERT statement and to align SELECT with INSERT:

ApexSQL Refactor options will give the same formatting for initial code, and additionally formatted by placing the ON, WHERE, FROM, and AS keywords on a new line

SELECT TOP 100
       p.productid,
       p.name,
       p.listprice,
       p.size,
       p.modifieddate,
       sod.unitprice,
       sod.unitpricediscount
FROM
     sales.salesorderdetail
     AS sod
     INNER JOIN production.product
     AS p ON sod.productid = p.productid
WHERE sod.unitprice > 1000;

RedGate® SQL Prompt – Schema statements formatting options

SQL Schema Statements or SQL DDL (Data Definition Language) provides maintenance of catalog objects for a SQL schema – tables, views and privileges.

SQL Prompt offers placing opening and closing parenthesis as well as the first definition on a new line, and indentation of the contents.

Code formatted using these options will look like below:

CREATE PROCEDURE dbo.usp_city ( @City SQL_VARIANT )
AS
    BEGIN
        SELECT
            *
        FROM
            adventureworks2012.person.address
        WHERE
            city = @City;
    END;

ApexSQL Refactor – Schema statements formatting options

ApexSQL Refactor offers options of manipulating statements body and parameters on a new line, as well as the option to place each parameter on a new line:

In ApexSQL Refactor parameters can be left in line with the keyword which is not offered by RedGate® SQL Prompt:

CREATE PROCEDURE dbo.usp_city( @City SQL_VARIANT )
AS
    BEGIN
        SELECT
               *
               FROM adventureworks2012.person.address
               WHERE city = @City;
    END;

RedGate® SQL Prompt – Expressions formatting options

One consideration in SQL formatting is how to handle expressions. RedGate® SQL Prompt offers options of placing operator at the start or at the end, closing parenthesis on a new line, placing each parameter on a new line for EXECUTE statements, manipulating the content of parenthesis, and inserting spaces and indents.

An example of code formatting with RedGate® SQL Prompt

CREATE FUNCTION sales.ufn_salesbystore ( @Storeid INT )
RETURNS TABLE
AS
RETURN
    ( SELECT
        p.productid ,
        p.name ,
        SUM(sd.linetotal) AS 'Total'
      FROM
        production.product AS p
        JOIN sales.salesorderdetail AS sd ON sd.productid = p.productid
        JOIN sales.salesorderheader AS sh ON sh.salesorderid = sd.salesorderid
        JOIN sales.customer AS c ON sh.customerid = c.customerid
      WHERE
        c.storeid = @Storeid
      GROUP BY
        p.productid ,
        p.name
    );
GO

ApexSQL Refactor – Expressions formatting options

In ApexSQL Refactor logical, arithmetic, and comparison operations can be treated differently in each case. For any expression case operators can be on separate lines, parentheses can be treated separately in different statements, and alignment and indentation can be separately tuned for each expression case:

Using the previous settings of placing the ON keyword on a new line, and manipulating opening and closing parentheses code can be additionally formatted:

CREATE FUNCTION sales.ufn_salesbystore ( @Storeid INT )
RETURNS TABLE
AS
RETURN
    ( SELECT
             p.productid ,
             p.name ,
             SUM(sd.linetotal) AS 'Total'
             FROM
                 production.product AS p
                 JOINsales.salesorderdetail AS sd 
                 ON sd.productid = p.productid
                 JOINsales.salesorderheader AS sh 
                 ON sh.salesorderid = sd.salesorderid
                 JOINsales.customer AS c 
                 ON sh.customerid = c.customerid
             WHERE c.storeid = @Storeid
             GROUP BY
                        p.productid ,
                        p.name
    );
GO

RedGate® SQL Prompt – Commas, parenthesis, tabs, wrapping

RedGate® SQL Prompt offers manipulation with commas and parentheses, as well as indents and wrapping

ApexSQL Refactor – Parenthesis, empty lines, miscellaneous, wrapping, spacing, indention

In addition to indentation and wrapping ApexSQL Refactor offers various spacing options. For example, spaces can added inside and outside parentheses, add spaces around assignment, arithmetic, and comparison operator, and before and after commas:

Empty lines can also be formatted or removed, parentheses can be manipulated in various combinations, and unnecessary brackets can be removed:

For example, having code with redundant brackets doesn’t have to be formatted manually:

SELECT ((10 + 10)) / 2; 

Using the Remove unnecessary brackets option this code will be formatted without changing the logic of the expression:

SELECT (10 + 10) / 2; 

This option is not supported by SQL Prompt.

Preview changes

One of the differences between these SQL formatters is displaying changes in the preview windows. Both SQL formatters have options to preview changes on both built-in script and the current SQL script in the query window, but ApexSQL Refactor highlights the changes in the preview window for both the built-in example and the current SQL query.

For example RedGate® SQL Prompt preview screen will not show the impact if some of the formatting settings are changed.

In ApexSQL Refactor any change in code will be shown for both the current query:

And the built–in example:

In the next article, additional formatting options will be described which can be found only in ApexSQL Refactor.

See also:

ApexSQL Refactor vs RedGate® SQL Prompt Refactors – Part 1 – Common refactors compared “Encapsulate As”
ApexSQL Refactor vs RedGate® SQL Prompt – Part 2 – Additional refactors
ApexSQL Refactor vs. RedGate® SQL Prompt – Part 4 – Additional formatting options
ApexSQL Refactor vs. RedGate® SQL Prompt – Part 5 – Additional formatting features

Disclaimer: All product and company names are trademarks™ or registered® trademarks of their respective holders. ApexSQL is not affiliated, associated, authorized, endorsed by, or in any way officially connected with them or any of its subsidiaries or its affiliates