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
- Capitalization
- Comments
- Statements
- Lists
- Expressions
- Arithmetic
- Comparison
- Logical
- Joins
- Flow control
- Obfustacion
- Shortcuts
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.:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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