Formatting option mockups and examples

Applies to
ApexSQL Refactor

Summary
This article describes the ways in which you can format SQL code using ApexSQL Refactor features and options. ApexSQL Refactor is add-in with over 200 formatting options and nearly 15 code refactors.

From this link you can download the most recent version of ApexSQL Refactor and play along trough the article.

Table of content

Formatting

In the Formatting tab, can be set indentation by using spaces or tabs. Aligns identifiers in the data statements like SELECT, INSERT or UPDATE statement by using Smart indent option. Additionally, space around assignment operators, before and after commas can be added. Using options under the Empty lines section empty lines can be removed or added before/after comments, before each statement, several empty lines can be changed into one empty line, etc.:


Back to top

Spacing

Add spaces around assigment operators: 

UPDATE Production.Product
  SET
      ListPrice=(ListPrice - 100);


UPDATE Production.Product
  SET
      ListPrice = (ListPrice - 100);

Add spaces before commas: 

INSERT INTO Production.UnitMeasure
(UnitMeasureCode,
 Name,
 ModifiedDate
)
VALUES
(N'FT2,
 N'Square Feet,
 '20080923'
);
GO


INSERT INTO Production.UnitMeasure
(UnitMeasureCode ,
 Name ,
 ModifiedDate
)
VALUES
(N'FT2',
 N'Square Feet',
 '20080923'
);
GO

 

Empty lines

Remove empty lines: 

SELECT * FROM le1
      
SELECT * FROM le2


SELECT * FROM le3


SELECT * FROM le1
SELECT * FROM le2
SELECT * FROM le3

Keep empty lines before/after comments: 


/* comment
*/




/* comment
*/

 

-- comment1
-- comment2

-- comment3




-- comment1
-- comment2
-- comment3

 


-- comment1



-- comment1

Keep empty lines before each statement : 

SELECT * FROM le1
      

SELECT * FROM le2



SELECT * FROM le3



SELECT * FROM le1

SELECT * FROM le2

SELECT * FROM le3

Change several empty lines into one empty line: 

SELECT * FROM le1
      

SELECT * FROM le2


SELECT * FROM le3


SELECT * FROM le1

SELECT * FROM le2

SELECT * FROM le3

 

Miscellaneous

Remove unnecessary brackets: 

UPDATE Production.Product
  SET
      ListPrice = (ListPrice - 100)


UPDATE Production.Product
  SET
      ListPrice = ListPrice - 100

Always use statement terminator: 

UPDATE Production.Product
  SET
      ListPrice = ListPrice - 100


UPDATE Production.Product
  SET
      ListPrice = ListPrice - 100;

Enclose identifiers in brackets: 

UPDATE Production.Product
  SET
      ListPrice = ListPrice – 100;


UPDATE Production.Product
  SET
      [ListPrice] = [ListPrice] - 100;

 

Capitalization

Under the Capitalization tab can be set capitalization style for keywords, data types, identifiers, system function and variables:


Back to top

DECLARE @Initials varchar(5); 
DECLARE @FirstName varchar(25); 
DECLARE @LastName varchar(25); 
SET @FirstName = 'Katie'; 
SET @Lastname = 'Melua'; 
SELECT @Initials = SUBSTRING(@FirstName, 1, 1) + SUBSTRING(@Lastname, 1, 1);


DECLARE @initials VARCHAR(5);
DECLARE @firstname VARCHAR(25);
DECLARE @lastname VARCHAR(25);
SET @firstname = 'Katie';
SET @lastname = 'Melua'; 
SELECT @initials = SUBSTRING(@firstname, 1, 1) + SUBSTRING(@lastname, 1, 1);

Comments

In the Comments tab, empty line before or after block comments can be added. Also, border above or below block comments can be set using any separator such (*, -, +, /). Additionally, all comments type can be change into block/inline comments or simply remove all comments from the code:

C:\Users\korisnik\Desktop\PDF_to_CS_article\Slike\Capture5.PNG

Back to top

Block comments

Insert empty line before/after block comments 

SELECT * FROM AWB
/*  comment
comment
*/
WHERE A=B


SELECT * FROM AWB

/*  comment
comment
*/

WHERE A=B

Add border above/below block comments using <-> 

SELECT * FROM AWB
/*  
comment
comment
*/
WHERE A=B


SELECT * FROM AWB
/*-------
  comment
comment
-------*/
WHERE A=B

Change all comments to block/line comments 

SELECT * FROM AWB
/*  
comment
comment
*/
WHERE A=B


SELECT * FROM AWB  
--comment
--comment
WHERE A=B

 

SELECT * FROM AWB  
--comment
--comment
WHERE A=B


SELECT * FROM AWB
/*comment
comment*/
WHERE A=B

Remove all block/line comments 

SELECT * FROM AWB
/*comment
comment*/
WHERE A=B


SELECT * FROM AWB
WHERE A=B

 

SELECT * FROM AWB  
--comment
--comment
WHERE A=B


SELECT * FROM AWB
WHERE A=B

Statements

Under the Statements tab, a lot of options can be set for data statements, nested selections, object definitions, aliases, etc. In the Data statements section, only statements that are longer than specified number of characters can be formatted. FROM, WHERE and SET clause can be moved to in a new line, align with the keyword, or indent by specified number of spaces. Parentheses in the nested selections can be placed into a new line, align with the keyword, or indented. The AS keyword can be aligned with the keyword or intended:


Back to top

Data statements

Move FROM clause to a new line 

SELECT * FROM Customers WHERE CustomerID = 1;


SELECT *
FROM Customers WHERE CustomerID = 1;

Align with keyword 

SELECT * FROM Customers WHERE CustomerID = 1;


SELECT * 
       FROM Customers WHERE CustomerID = 1;

Move WHERE clause to a new line

Align with FROM 

SELECT * FROM Customers WHERE CustomerID = 1;


SELECT * FROM Customers
         WHERE CustomerID = 1;

Move SET clause to a new line

Align with keyword 

UPDATE Sales.SalesOrderHeader SET
                                   SalesPersonID = 1;


UPDATE Sales.SalesOrderHeader
       SET
           SalesPersonID = 1;

 

Nested selects

Format parentheses placement

Move opening parentheses to a new line

Align with keyword

SELECT * FROM (SELECT * FROM AWBud)

SELECT * FROM 
              (SELECT * FROM AWBud)

Indent 3 spaces 

SELECT * FROM 
(SELECT * FROM AWBud)


SELECT * FROM 
  (SELECT * FROM AWBud)

Move closing parentheses to a new line

Align to the end of previous line 

SELECT * FROM (SELECT * FROM AWBud)

SELECT * FROM (SELECT * FROM AWBud
                                   )

Align with keyword 

SELECT * FROM (SELECT * FROM AWBud)


SELECT * FROM (SELECT * FROM AWBud
             )

Move code with parentheses to a new line

Place at same position 

SELECT * FROM (SELECT * FROM AWBud)

SELECT * FROM (
               SELECT * FROM AWBud)

 

Aliases

Align all alias names 

SELECT alpha a, col1 c
       bta AS b, 
       gammaone g


SELECT alpha    a, col1 c
       bta AS   b, 
       gammaone g

Always use AS for aliases in SELECT statements 

SELECT alpha a, col1 c
       bta AS b, 
       gammaone g


SELECT alpha AS a, col1 AS c
       bta AS b, 
       gammaone AS g

 

Place AS on new line

Indent 

SELECT column1 
               AS a, 
       column2 
               AS b
FROM spt_values 
                AS c, 
     table1 
            AS d


SELECT column1 
           AS a, 
       column2 
           AS b
FROM spt_values 
           AS c, 
     table1 
           AS d

Align with keyword 

SELECT * 
      FROM Table1 AS a WHERE Datag=Sept


SELECT * 
      FROM Table1 
          AS a WHERE Datag=Sept

 

Other

Always use INTO in INSERT statements 

INSERT HResources (ID, Office, FName)
VALUES (1,200,Charles)


INSERT INTO HResources (ID, Office, FName)
VALUES (1,200,Charles)

Place body on new line 

CREATE PROCEDURE HR.GetEmployees @LastN nvarchar(50) = N'D%'
AS SELECT FirstN, LastN FROM HR.vEmployeeDepartment
GO


CREATE PROCEDURE HR.GetEmployees @LastN nvarchar(50) = N'D%'
AS 
SELECT FirstN, LastN FROM HR.vEmployeeDepartment
GO

Indent 

CREATE PROCEDURE HR.GetEmployees @LastN nvarchar(50) = N'D%'
AS SELECT FirstN, LastN FROM HR.vEmployeeDepartment
GO


CREATE PROCEDURE HR.GetEmployees @LastN nvarchar(50) = N'D%'
AS 
    SELECT FirstN, LastN FROM HR.vEmployeeDepartment
GO

Lists

Under the Lists tab are sub-tabs in which can be set up formatting style for tables, columns, values, where in values, assignments, variables, parameters.

Tables

In the Tables sub-tab, place the table item list can be set to a new table, align table item list with a keyword or indented them. Additionally, you can place each item from the list on a new line and place a comma before or after each item:


Back to top

Format lists

Place list on a new line

Align with keyword 

SELECT c.LastName,
       c.FirstName,
       at.ModifiedDate
FROM Person.Person c, Person.AddressType at;


SELECT c.LastName,
       c.FirstName,
       at.ModifiedDate
FROM
     Person.Person c, Person.AddressType at;

Place each item on a new line

Place comma after each item

SELECT c.LastName,
       c.FirstName,
       at.ModifiedDate
FROM
     Person.Person c, Person.AddressType at;


SELECT c.LastName,
       c.FirstName,
       at.ModifiedDate
FROM
     Person.Person c,
     Person.AddressType at;

Place each item on a new line

Place comma before each item 

SELECT c.LastName,
       c.FirstName,
       at.ModifiedDate
FROM
     Person.Person c, Person.AddressType at;


SELECT c.LastName,
       c.FirstName,
       at.ModifiedDate
FROM
     Person.Person c
   , Person.AddressType at;

Columns

In the Columns tab, the column list can be set a list on a new line, aligned with the keyword. Also, each item from the column list can be placed at a new line with a comma after or before each column. Additionally, opening and closing parentheses can be moved to a new line, aligned with the keyword, indented etc. Place inside/otside parentheses can be added:


Back to top

Format lists

Place list on a new line

Align with keyword 

SELECT p.LastName a, p.FirstName
FROM Person.Person;


SELECT
       p.LastName a, p.FirstName
FROM Person.Person;

Place list on a new line

Indent 0 spaces 

SELECT
       p.LastName a, p.FirstName
FROM Person.Person;


SELECT
p.LastName a, p.FirstName
FROM Person.Person;

Place each item on a new line

Place comma after each item 

SELECT p.LastName a, p.FirstName
FROM Person.Person;


SELECT p.LastName a, 
       p.FirstName
FROM Person.Person;

Place each item on a new line

Place comma before each item 

SELECT p.LastName a, p.FirstName
FROM Person.Person;


SELECT p.LastName a 
      ,p.FirstName
FROM Person.Person;

 

Format parentheses placement

Move opening parentheses to a new line

Align with keyword 

INSERT INTO Person(FirstName, LastName, Age)
VALUES (PersonFirstName, PersonLastName, PersonAge)


INSERT INTO Person
       (FirstName, LastName, Age)
VALUES (PersonFirstName, PersonLastName, PersonAge)

Move opening parentheses to a new line

Indent 0 spaces 

INSERT INTO Person(FirstName, LastName, Age)
VALUES (PersonFirstName, PersonLastName, PersonAge)


INSERT INTO Person
(FirstName, LastName, Age)
VALUES (PersonFirstName, PersonLastName, PersonAge)

Move closing parentheses to a new line

Align with keyword 

INSERT INTO Person(FirstName, LastName, Age)
VALUES (PersonFirstName, PersonLastName, PersonAge)


INSERT INTO Person(
  FirstName, LastName, Age
       )
VALUES (PersonFirstName, PersonLastName, PersonAge)

Move closing parentheses to a new line

Align with opening parentheses 

INSERT INTO Person(
  FirstName, LastName, Age
 )
VALUES (PersonFirstName, PersonLastName, PersonAge)


INSERT INTO Person(
  FirstName, LastName, Age
                  )
VALUES (PersonFirstName, PersonLastName, PersonAge)

Move closing parentheses to a new line

Indent 0 spaces 

INSERT INTO Person(
  FirstName, LastName, Age
 		)
VALUES (PersonFirstName, PersonLastName, PersonAge)


INSERT INTO Person(
  FirstName, LastName, Age
)
VALUES (PersonFirstName, PersonLastName, PersonAge)

Values

Under the Values sub- tab, complete value list can be move to a new line, aligned or indented. Also, each item from the list can be placed to a new line with a comma before/after each item. Opening and closing parentheses can be placed to a new line aligned with the keyword or indented. Additionally, closing parentheses can be aligned with the opening parentheses. Spaces inside/outside parentheses can be added:


Back to top

Format lists

Place list on a new line

Align with keyword 

VALUES (N'FT2', N'Square Feet ');

VALUES (
       N'FT2', N'Square Feet ');ge)

Indent 0 spaces 

VALUES (N'FT2', N'Square Feet ');


VALUES (N'FT2', 
N'Square Feet ');

Place each item on a new line

Place comma after each item 

VALUES (N'FT2', N'Square Feet ');

VALUES (N'FT2', 
        N'Square Feet ');

Place each item on a new line

Place comma before each item 

VALUES (N'FT2', N'Square Feet ');

VALUES (N'FT2' 
       ,N'Square Feet ');

Format parentheses placement

Move opening parentheses to a new line

Place at same position 

VALUES (N'FT2', N'Square Feet'), (N'Y', N'Yards', '20080923');


VALUES
      (N'FT2', N'Square Feet ', '20080923'),
                                             (N'Y', N'Yards', '20080923')

Align with keyword 

VALUES (N'FT2', N'Square Feet '), (N'Y', N'Yards', '20080923');

VALUES 
      (N'FT2', N'Square Feet'),
      (N'Y', N'Yards', '20080923');

Move closing parentheses to a new line

Align to the end of previous line 

VALUES (N'FT2', N'Square Feet');

VALUES (N'FT2', N'Square Feet'
                              );

Align with keyword 

VALUES (N'FT2', N'Square Feet');


VALUES (N'FT2', N'Square Feet'
      );

Where in values

Under the Where in valuse sub-tab, the WHERE IN value list can be placed at a new line, aligned with the keyword or indented. Each item in the list can be moved to a new line with commas after/before each item. Additionally, opening and closing parentheses can be moved to a new line, aligned with the keyword,indented. Also, closing parentheses can be aligned with opening parentheses. Space inside/outside parentheses can be added:


Back to top

Format lists

Place list on a new line

Align with keyword 

SELECT ProductID
FROM Production.Product AS p
WHERE ProductNumber IN (1, 2, 3);


SELECT ProductID
FROM Production.Product AS p
WHERE ProductNumber IN (
                       1, 2, 3);

Indent 0 spaces 

SELECT ProductID
FROM Production.Product AS p
WHERE ProductNumber IN (1, 2, 3);


SELECT ProductID
FROM Production.Product AS p
WHERE ProductNumber IN (
1, 2, 3);

Place each item on a new line

Place comma after each item 

SELECT ProductID
FROM Production.Product AS p
WHERE ProductNumber IN (1, 2, 3);


SELECT ProductID
FROM Production.Product AS p
WHERE ProductNumber IN (1,
                        2,
                        3);

Place comma before each item 

SELECT ProductID
FROM Production.Product AS p
WHERE ProductNumber IN (1, 2, 3);


SELECT ProductID
FROM Production.Product AS p
WHERE ProductNumber IN (1
                      , 2
                      , 3);

 

Format parentheses placement

Move opening parentheses to a new line

Place at same position 

SELECT ProductID
FROM Production.Product AS p
WHERE ProductNumber IN (1, 2, 3);


SELECT ProductID
FROM Production.Product AS p
WHERE ProductNumber IN 
                      (1, 2, 3);

Indent 0 spaces 

SELECT ProductID
FROM Production.Product AS p
WHERE ProductNumber IN 
   (1, 2, 3);


SELECT ProductID
FROM Production.Product AS p
WHERE ProductNumber IN 
(1, 2, 3);

Move closing parentheses to a new line

Align to the end of previous line 

SELECT ProductID
FROM Production.Product AS p
WHERE ProductNumber IN (1, 2, 3);


SELECT ProductID
FROM Production.Product AS p
WHERE ProductNumber IN (1, 2, 3
                                );

Align with opening parentheses 

SELECT ProductID
FROM Production.Product AS p
WHERE ProductNumber IN (1, 2, 3);


SELECT ProductID
FROM Production.Product AS p
WHERE ProductNumber IN (1, 2, 3
                       );

Assignments

At the Assignment sub-tab, assignments list can be placed to a new line, aligned or indented. Each item from the list can be moved to a new line with comma placed after/before each item. Opening and closing parentheses can be placed to a new line aligned with the keyword or indented. Spaces inside/outside parentheses can be set:


Back to top

Format lists

Place list on a new line

Align with keyword

CREATE DATABASE Sales ON(SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5);

CREATE DATABASE Sales ON(
      SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5);

Indent 0 spaces 

CREATE DATABASE Sales ON(SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5);

CREATE DATABASE Sales ON(
SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5);

Place each item on a new line

Place comma after each item 

CREATE DATABASE Sales ON(SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5);

CREATE DATABASE Sales ON(SIZE = 10, 
                         MAXSIZE = 50, 
                         FILEGROWTH = 5);

Place each item on a new line

Place comma before each item 

CREATE DATABASE Sales ON(SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5);

CREATE DATABASE Sales ON(SIZE = 10, 
                       , MAXSIZE = 50, 
                       , FILEGROWTH = 5);

 

Format parentheses placement

Move opening parentheses to a new line

Place at same position 

CREATE DATABASE Sales ON(SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5);

CREATE DATABASE Sales ON
                        (SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5);

Align with keyword 

CREATE DATABASE Sales ON(SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5);

CREATE DATABASE Sales ON
      (SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5);

Move closing parentheses to a new line

Align to the end of previous line 

CREATE DATABASE Sales ON(SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5);

CREATE DATABASE Sales ON(SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5
                                                                );

Align with keyword 

CREATE DATABASE Sales ON(SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5);

CREATE DATABASE Sales ON(SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5
       );

Align with opening parentheses 

CREATE DATABASE Sales ON(SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5);

CREATE DATABASE Sales ON(SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5
 			);

Variables

In the Variables sub-tab, variables list can be placed on a new line, aligned with the keyword or indented, Each item can be moved to a new line with a comma after/before each item:


Back to top

Format lists

Place list on a new line

Align with keyword 

DECLARE @NInitials VARCHAR(25), @FirstName VARCHAR(25), @LastName VARCHAR(25);

DECLARE
       @NInitials VARCHAR(25), @FirstName VARCHAR(25), @LastName VARCHAR(25);

Indent 0 spaces 

DECLARE
       @NInitials VARCHAR(25), @FirstName VARCHAR(25), @LastName VARCHAR(25);


DECLARE
@NInitials VARCHAR(25), @FirstName VARCHAR(25), @LastName VARCHAR(25);

Place each item on a new line

Place comma after each item 

DECLARE @NInitials varchar(25), @FirstName varchar(25), @LastName varchar(25) ;

DECLARE @NInitials varchar(25),
        @FirstName varchar(25),
        @LastName varchar(25) ;

Place comma before each item 

DECLARE @NInitials varchar(25),
        @FirstName varchar(25),
        @LastName varchar(25) ;


DECLARE @NInitials varchar(25)
      , @FirstName varchar(25)
      , @LastName varchar(25) ;

Parameters

Using options under the Parameter sub-tub, the parameters list can be placed to a new line, aligned with keyword, indented,and each item from the list can be moved to a new line with commas before/after each item. Also, opening and closing parenthases can be moved to a new line, aligned with the keyword or indented. Additionally, space before and after parenthases can be added:


Back to top

Format lists

Place list on a new line

Align with keyword 

CREATE TABLE Person(FirstName VARCHAR(50), LastName VARCHAR(50));

CREATE TABLE Person(
             FirstName VARCHAR(50), LastName VARCHAR(50));

Indent 0 spaces 

CREATE TABLE Person(
 FirstName VARCHAR(50), LastName VARCHAR(50));


CREATE TABLE Person(
FirstName VARCHAR(50), LastName VARCHAR(50));

Place each item on a new line

Place comma after each item 

CREATE TABLE Person(FirstName VARCHAR(50), LastName VARCHAR(50));

CREATE TABLE Person(FirstName VARCHAR(50), 
                    LastName VARCHAR(50));

Place comma before each item 

CREATE TABLE Person(FirstName VARCHAR(50), LastName VARCHAR(50));

CREATE TABLE Person(FirstName VARCHAR(50) 
           , LastName VARCHAR(50));

Do not indent comma 

CREATE TABLE Person(FirstName VARCHAR(50), LastName VARCHAR(50));

CREATE TABLE Person(FirstName VARCHAR(50)
,                   LastName VARCHAR(50));

Format parentheses placement

Move opening parentheses to a new line

Place at same position 

CREATE TABLE Person(FirstName VARCHAR(50), LastName VARCHAR(50));

CREATE TABLE Person
                   (FirstName VARCHAR(50), LastName VARCHAR(50));

Align with keyword 

CREATE TABLE Person (FirstName VARCHAR(50), LastName VARCHAR(50));

CREATE TABLE Person
            (FirstName VARCHAR(50), LastName VARCHAR(50));

Move closing parentheses to a new line

Align to the end of previous line 

CREATE TABLE Person(FirstName VARCHAR(50), LastName VARCHAR(50));

CREATE TABLE Person(FirstName VARCHAR(50), LastName VARCHAR(50)
                                                               );

Align with keyword 

CREATE TABLE Person(FirstName VARCHAR(50), LastName VARCHAR(50));

CREATE TABLE Person(FirstName VARCHAR(50), LastName VARCHAR(50)
             );

Align with opening parentheses 

CREATE TABLE Person(FirstName VARCHAR(50), LastName VARCHAR(50));

CREATE TABLE Person(FirstName VARCHAR(50), LastName VARCHAR(50)
                    );

Expressions

Under the Expressions tab, numerous options can be set for arithmetic, comparison, and logical operations

Arithmetic

In the Arithmetic sub-tab, can be set to format operations only if they are longer then specified number of characters. Also, arithmetic operation can be placed to a new line, show operations on multiple lines, add spaces around operators. Additionally, opening and closing parentheses can be placed to a new line, aligned with the keyword, indented, etc. Space can be set outside/inside parentheses:


Back to top

Format operations

Don't format operations shorter than 22 characters

Move operation to a new line

Place at same position 

WHERE(((oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

WHERE(((oldsalary<=5) AND
                          oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

Indent 0 spaces 

WHERE(((oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

WHERE(((oldsalary<=5) AND
oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

Show operations on multiple lines

Place operator before operand 

WHERE(((oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

WHERE(((oldsalary<=5) AND oldsalary
                          +@COLI
                          *oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

Place operator after operand 

WHERE(((oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

WHERE(((oldsalary<=5) AND oldsalary+
                          @COLI*
                          oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

Place operator on separate line 

WHERE(((oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

WHERE(((oldsalary<=5) AND oldsalary
                          +
                          @COLI
                          *
                          oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

Format parentheses placement

Move opening parentheses to a new line

Place at same position 

WHERE(((oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

WHERE(((oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR
                                                           (
                                                            (
                                                             (@COLI+10)-45+125)/5)<1000);

Align with keyword 

WHERE(((oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

WHERE(((oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR
      (
      (
      (@COLI+10)-45+125)/5)<1000);

Move closing parentheses to a new line

Align to the end of previous line 

WHERE(((oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

WHERE(((oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10
                                                                      )-45+125
                                                                              )/5
                                                                                 )<1000);

Align with opening parentheses 

WHERE(((oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

WHERE(((oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10
                                                              )-45+125
                                                             )/5
                                                            )<1000);

Comparison

In the Comparison sub-tab, can be set to format operations only if are longer then then specified number of characters. Operation can be placed to a new line, indented, show operations on multiple lines, spaces around operators can be added. Additionally,opening and closing parenthases can be moved to a new line, algined with keyword, indented. Also, spces inside/outside parentheses can be added:


Back to top

Format operations

Move operation to a new line

Place at same position 

WHERE(((oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

WHERE(((oldsalary<=5) AND
                          oldsalary+@COLI*oldsalary<10) OR
                                                           (((@COLI+10)-45+125)/5)<1000);

Indent 0 spaces 

WHERE(((oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

WHERE(((oldsalary<=5) AND
oldsalary+@COLI*oldsalary<10) OR
(((@COLI+10)-45+125)/5)<1000);

Show operations on multiple lines

Place operator before operand 

WHERE(((oldsalary<=5) AND
oldsalary+@COLI*oldsalary<10) OR
(((@COLI+10)-45+125)/5)<1000);


WHERE(((oldsalary<=5) AND
oldsalary+@COLI*oldsalary
<10) OR
(((@COLI+10)-45+125)/5)
<1000);

Place operator after operand 

WHERE(((oldsalary<=5) AND
oldsalary+@COLI*oldsalary<10) OR
(((@COLI+10)-45+125)/5)<1000);


WHERE(((oldsalary<=5) AND
oldsalary+@COLI*oldsalary<
10) OR
(((@COLI+10)-45+125)/5)<
1000);

Place operator on separate line 

WHERE(((oldsalary<=5) AND
oldsalary+@COLI*oldsalary<10) OR
(((@COLI+10)-45+125)/5)<1000);


WHERE(((oldsalary<=5) AND
oldsalary+@COLI*oldsalary
<
10) OR
(((@COLI+10)-45+125)/5)
<
1000);

 

Format parentheses placement

Move opening parentheses to a new line

Place at same position 

WHERE(((oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

WHERE((
       (oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

Move closing parentheses to a new line

Align to the end of previous line 

WHERE(((oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

WHERE(((oldsalary<=5
                    ) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

Align with opening parentheses 

WHERE(((oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

WHERE(((oldsalary<=5
       ) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

Logical

Under the Logical sub-tab, can be set to format operations only if are longer then then specified number of characters. Operation can be moved to a new line, indented, show opreations on multiple lines. Opening and closing parenthases can be placed to a new line, indented, aligned with keyword. Additionally, closing parentheses can be aligned with opening parenthases. Space inside or outside parenthases can be set:


Back to top

Format operations

Move operation to a new line

Place at same position 

WHERE(((oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

WHERE(
     (
     (oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

Show operations on multiple lines

Place operator before operand 

WHERE(
     (
     (oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);


WHERE(
     (
     (oldsalary<=5)
     AND oldsalary+@COLI*oldsalary<10)
     OR (((@COLI+10)-45+125)/5)<1000);

Place operator after operand 

WHERE(
     (
     (oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);


WHERE(
     (
     (oldsalary<=5) AND
     oldsalary+@COLI*oldsalary<10) OR
     (((@COLI+10)-45+125)/5)<1000);

Place operator on separate line 

WHERE(
     (
     (oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);


WHERE(
     (
     (oldsalary<=5)
     AND
     oldsalary+@COLI*oldsalary<10)
     OR
     (((@COLI+10)-45+125)/5)<1000);

Move opening parentheses to a new line

Place at same position 

WHERE(((oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

WHERE
     (
      ((oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

Move closing parentheses to a new line

Align to the end of previous line 

WHERE(((oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

WHERE(((oldsalary<=5) AND oldsalary+@COLI*oldsalary<10
                                                      ) OR (((@COLI+10)-45+125)/5)<1000
                                                                                       );

Align with opening parentheses 

WHERE(((oldsalary<=5) AND oldsalary+@COLI*oldsalary<10) OR (((@COLI+10)-45+125)/5)<1000);

WHERE(((oldsalary<=5) AND oldsalary+@COLI*oldsalary<10
      ) OR (((@COLI+10)-45+125)/5)<1000
     );

Joins

At Joins sub-tab, can be set to format only JOIN statement that are longer than specified number of characters. First table in the JOIN statement can be placed on new line, aligned with keyword, indented. Also, JOIN keyword cab be moved to a line, aligned with first table, indented. Joined object can be placed on new line. The ON keyword cab be moved to a new line, aligned with the beginning of the previous keyword, aligned with JOIN keyword, indented. Nested joins can be placed to a new line, aligned with the beginning of the previous keyword, aligned with previous JOIN keyword, aligned with first table, indented:


Back to top

Format joins

Place first table on new line

Align with previous keyword 

SELECT * FROM P.A JOIN S.C ON P.A.ID =S.C.ID

SELECT * FROM 
          P.A JOIN S.C ON P.A.ID =S.C.ID

Place JOIN keyword in new line

Align with first table 

SELECT * FROM P.A JOIN S.C ON P.A.ID =S.C.ID

SELECT * FROM P.A 
              JOIN S.C ON P.A.ID =S.C.ID

Place joined object on new line 

SELECT * FROM P.A JOIN S.C ON P.A.ID =S.C.ID

SELECT * FROM P.A JOIN 
                  S.C ON P.A.ID =S.C.ID

Place ON on new line

Align with the beginning of the previous keyword 

SELECT * FROM P.A JOIN S.C ON P.A.ID =S.C.ID

SELECT * FROM P.A JOIN S.C 
             ON P.A.ID =S.C.ID

Align with first table 

SELECT * FROM P.A JOIN S.C ON P.A.ID =S.C.ID

SELECT * FROM P.A JOIN S.C 
   	      ON P.A.ID =S.C.ID

Place nested join operations on separite lines

Align with the beginning of the previous keyword 

SELECT * FROM Prst.A JOIN S.C ON Prst.A.ID=S.C.ID
JOIN S.s ON C.D.ID=S.sId;


SELECT * FROM Prst.A JOIN S.C ON Prst.A.ID=S.C.ID
         JOIN S.s ON C.D.ID=S.sId;

Align with previous JOIN keyword 

SELECT * FROM Prst.A JOIN S.C ON Prst.A.ID=S.C.ID
JOIN S.s ON C.D.ID=S.sId;


SELECT * FROM Prst.A JOIN S.C ON Prst.A.ID=S.C.ID
   		     JOIN S.s ON C.D.ID=S.sId;

Flow control

Under the Flow control sub-tab, WHEN, THEN, ELSE condition keywords can be placed on a new line. BEGIN and END keywords can be set to be used in IF statement or in stored procedure. Also, BEGIN /END block can be indented. Additionally, code inside condition statement can be indented:


Back to top

Condition keywords

Place WHEN on new line 

CASE ProductLine WHEN 'S' THEN 'Other' ELSE'NFS'

CASE ProductLine 
                 WHEN 'S' THEN 'Other' ELSE 'NFS'

Indent 

CASE ProductLine WHEN 'S' THEN 'Other' ELSE'NFS'

CASE ProductLine 
    WHEN 'S' THEN 'Other' ELSE'NFS'

Place THEN on a new line 

CASE ProductLine 
    WHEN 'S' THEN 'Other' ELSE 'NFS'


CASE ProductLine 
    WHEN 'S' 
             THEN 'Other' ELSE 'NFS'

Indent 

CASE ProductLine 
    WHEN 'S' THEN 'Other' ELSE'NFS'


CASE ProductLine 
    WHEN 'S' 
    THEN 'Other' ELSE'NFS'

Place ELSE on a new line 

CASE ProductLine 
    WHEN 'S' 
    THEN 'Other' ELSE'NFS'


CASE ProductLine 
    WHEN 'S' 
    THEN 'Other' 
                 ELSE'NFS'

Indent 

CASE ProductLine 
    WHEN 'S' 
    THEN 'Other' ELSE 'NFS'


CASE ProductLine 
    WHEN 'S' 
    THEN 'Other' 
    ELSE'NFS'

Begin and End

Always use BEGIN and END in IF statements 

IF @A > 3 
    PRINT @A
ELSE
    PRINT @B


IF @A > 3
    BEGIN
    	PRINT @A
    END
ELSE
    BEGIN
    	PRINT @B
    END

Always use BEGIN and END in Stored Procedures 

CREATE PROCEDURE ABC
AS
SELECT * FROM AWB
GO


CREATE PROCEDURE ABC
AS
   BEGIN
SELECT * FROM AWB;
   END;
GO

Obfustacion

In the Obfustacion sub-tab, the SQL code can be set to be less readable by using the Wrapping, Randomize keywords case or Remove comments options:


Back to top

CREATE PROCEDURE spInsertOrUpdateProduct
-- Input parameters --
@ProductNumber nVarChar(25),@ListPrice Money
AS
IF EXISTS(SELECT * FROM Production.Product WHERE ProductNumber=@ProductNumber AND ListPrice>1000)
UPDATE Production.Product SET ListPrice=(ListPrice-100) WHERE ProductNumber=@ProductNumber
ELSE
INSERT INTO Production.Product(ProductNumber,ListPrice)
SELECT @ProductNumber,@ListPrice
GO
SELECT * FROM Production.Product
GO
INSERT INTO Production.UnitMeasure(UnitMeasureCode,Name,ModifiedDate)
VALUES(N'FT2',N'Square Feet','20080923');
GO


cReATE PrOceDUrE spInsertOrUpdateProduct @ProductNumber nVarChar(25),@ListPrice
Money AS iF ExistS(seLeCT * fROm Production.Product wheRE ProductNumber=
@ProductNumber ANd ListPrice>1000) uPdAtE Production.Product seT ListPrice=(
ListPrice-100) WheRE ProductNumber=@ProductNumber elSe INSeRt InTO Production.
Product(ProductNumber,ListPrice) SeLEct @ProductNumber,@ListPrice go sElEcT *
fROM Production.Product GO INseRT iNtO Production.UnitMeasure(UnitMeasureCode,
Name,ModifiedDate) VAlUes(N'FT2',N'Square Feet','20080923'); go

Shortcuts


Back to top

 

Related posts:

  1. Remote installation of ApexSQL Audit auditing instance (service)
  2. ApexSQL Defrag installation and troubleshooting guide
  3. Migration scripts introduction and general review
  4. How to generate database documentation from multiple SQL servers
  5. ApexSQL monitoring tools alerts