ApexSQL Refactor vs. RedGate® SQL Prompt – Part 4 – Additional formatting options

In previous article ApexSQL Refactor vs. RedGate® SQL Prompt – Part 3 – Common formatting options compared the main differences in T-SQL formatting options supported by both ApexSQL Refactor and RedGate® SQL Prompt were presented. In this article, additional formatting options will be described, which are found only in ApexSQL Refactor.

ApexSQL Refactor is a FREE SQL Server Management Studio and Visual Studio add-in which formats SQL code and SQL scripts, using nearly 200 formatting options and 11 SQL code refactors.

The Joins option

As SQL joins introduce more complex code, ApexSQL Refactor has the Joins option for formatting JOIN statements using various formatting combinations:

ApexSQL Refactor - Option for formatting JOIN statements using various formatting combinations

The JOIN keyword can be placed at the end of the line, at the start of the line, or placed in a separate row.

In the following example the JOIN keyword is placed on a separate line and the nested JOIN statement is aligned with the previous JOIN statement

SELECT firstname ,
       lastname ,
       title ,
       phonenumber ,
       passwordhash
FROM person.person
INNER JOIN person.personphone
ON person.businessentityid = personphone.businessentityid
LEFT JOIN person.password
ON person.businessentityid = password.businessentityid
WHERE lastname = 'M';

Beside the option of aligning nested JOIN statements with the previous JOIN statement, nested JOIN statements can be indented for a custom number of spaces like in the following example:

SELECT firstname ,
       lastname ,
       title ,
       phonenumber ,
       passwordhash
FROM person.person
INNER JOIN person.personphone
ON person.businessentityid = personphone.businessentityid
      LEFT JOIN person.password
ON person.businessentityid = password.businessentityid
WHERE lastname = 'M';

The ON keywords can be aligned with the JOIN statement or indented for a custom number of spaces like in the code below:

SELECT firstname ,
       lastname ,
       title ,
       phonenumber ,
       passwordhash
FROM person.person
INNER JOIN person.personphone
   ON person.businessentityid = personphone.businessentityid
      LEFT JOIN person.password
   ON person.businessentityid = password.businessentityid
WHERE lastname = 'M';

The Variables formatting options

Options for formatting variables in ApexSQL Refactor are: placing first variable on a new line, and placing each variable on a new line, as well as placing commas before or after each row:

Options for formatting variables in ApexSQL Refactor

In this example, the first variable is in line with the DECLARE keyword, and each variable is placed in a new line with commas after each row:

DECLARE @Startdate DATETIME
	,@Enddate DATE
	,@Modifieddate DATETIME;
DECLARE @Startdateonly DATETIME;
DECLARE @Enddateonly DATE;

Formatting combination of placing all variables on a new line, and a comma before each row would give the next code formatting:

DECLARE
       @Startdate DATETIME
     , @Enddate DATE
     , @Modifieddate DATETIME;
DECLARE
       @Startdateonly DATETIME;
DECLARE
       @Enddateonly DATE;

The Value lists option

A table value constructor specifies a set of row values that will be inserted into a table:

A table value constructor specifies a set of row values to be inserted into a table

Value lists can be formatted in various ways:

  1. By leaving the value list, row values, and each value in line with the VALUES statement and placing each row on a new line:

    INSERT INTO Production.UnitMeasure
VALUES ( N'FT2' , N'Square Feet ' , '20080923' ) , 
	     ( N'Y' , N'Yards' , '20080923' ) , 
	     ( N'Y3' , N'Cubic Yards' , '20080923' ) ;

  2. Placing each value on a new line:

    INSERT INTO Production.UnitMeasure
VALUES ( N'FT2' ,
         N'Square Feet ' ,
         '20080923' ) ,
       ( N'Y' ,
         N'Yards' ,
         '20080923' ) ,
       ( N'Y3' ,
         N'Cubic Yards' ,
         '20080923' ) ;

  3. Or by placing a value list on a new line aligned with the keyword or indented for custom number of spaces and placing row values on a new line:

    INSERT INTO Production.UnitMeasure
VALUES
       (
       N'FT2' ,
       N'Square Feet ' ,
       '20080923' ) ,
       (
       N'Y' ,
       N'Yards' ,
       '20080923' ) ,
       (
       N'Y3' ,
       N'Cubic Yards' ,
       '20080923' ) ;

Commas can be placed before or after each row

Flow control

Another SQL formatting issue is how to handle code blocks such as IF/ELSE/WHEN. ApexSQL Refactor offers options to place condition keywords on a new line with optional indentation and wrapping up IF statements and stored procedures with a BEGIN/END block:

ApexSQL Refactor formatting options - Flow control

Following formatting combination places WHEN, THEN, and ELSE keywords on a new line and indents them for four spaces. The number of spaces can be customized:

CREATE PROCEDURE ufn_sqlformatting (@Storeid INT)
AS
BEGIN
	IF @Storeid > 10
	BEGIN
		SELECT productnumber AS p
			,NAME AS n
			,'Price Range' = CASE 
				WHEN listprice = 0
					THEN 'Mfg item - not for resale'
				WHEN listprice < 50
					THEN 'Under $50'
				ELSE 'Over $1000'
				END
		FROM production.product;
	END;
	ELSE
	BEGIN
		PRINT 'Incorrect Store ID';
	END;
END;

The Always use BEGIN and END in IF statements option will wrap up the IF statement with BEGIN and END keywords. For example, using this option the following code:

IF year(getdate()) = '2013'
	PRINT 'This time next year'

Will be formatted as:

IF YEAR(GETDATE()) = '2013'
BEGIN
	PRINT 'This time next year'
END;

The Always use BEGIN and END in all Stored Procedures option will add BEGIN and END keywords to all stored procedure statements. Using this option in the following CREATE PROCEDURE statement:

CREATE PROCEDURE SQLFormatter
AS
IF year(getdate()) = '2013'
	PRINT 'This time next year'

The BEGIN and END keywords will be added at the appropriate places:

CREATE PROCEDURE sqlformatter
AS
BEGIN
	IF YEAR(GETDATE()) = '2013'
	BEGIN
		PRINT 'This time next year'
	END;
END;

The Comments option

Comments are text strings in program code which are not executed. SQL Server supports two types of commenting characters: — and /* … */.

In ApexSQL Refactor comments can be formatted by inserting an empty line before and after block comments or changing all comments to block or to line comments:

Using the Add border above block comments and Add border below block comments, comments blocks can be visually separated from the rest of the SQL code with the custom chosen character without the need to type the whole line of characters manually.

Before:

SELECT firstname
     , lastname
     , title
     , phonenumber
     , passwordhash
FROM person.person
INNER JOIN person.personphone
ON person.businessentityid = personphone.businessentityid 
/* LEFT OUTER JOIN person.password 
                ON person.businessentityid = password.businessentityid */
WHERE lastname = 'M';

After:

SELECT firstname
     , lastname
     , title
     , phonenumber
     , passwordhash
FROM person.person
INNER JOIN person.personphone
ON person.businessentityid = personphone.businessentityid 
/**********************************************************************
 LEFT OUTER JOIN person.password 
              ON person.businessentityid = password.businessentityid 
**********************************************************************/
WHERE lastname = 'M';

T-SQL code can be cleaned from commented code using the Remove all block comments
option:

SELECT firstname
     , lastname
     , title
     , phonenumber
     , passwordhash
FROM person.person
INNER JOIN person.personphone
ON person.businessentityid = personphone.businessentityid 
WHERE lastname = 'M';

Line comments can also be removed using the Remove all line comments option

In the sequel to this article we will present additional formatting features 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 Formatting – Part 3 – Common formatting options compared
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

 

Related posts:

  1. ApexSQL vs Redgate® comparative articles
  2. ApexSQL Refactor vs RedGate® SQL Prompt Formatting – Part 3 – Common formatting options compared
  3. ApexSQL Refactor vs. RedGate® SQL Prompt – Part 5 – Additional formatting features
  4. ApexSQL Refactor vs RedGate® SQL Prompt – Part 2 – Additional refactors
  5. ApexSQL Refactor vs RedGate® SQL Prompt Refactors – Part 1 – Common refactors compared “Encapsulate as”
November 13, 2013