CODE SPREAD

A post by guest author Milena Petrovic

Nobody likes to read a wall of text, even when it’s just plain text. When it comes to reading code, the problem is even bigger. Code can have different formatting styles, which could make your job either easier or more difficult. It can make code difficult to decipher and understand. A clean and neat SQL is read faster than an inconsistently written SQL; SQL reviewing and troubleshooting is more efficient; joint development efforts are more effective; handing off projects from one team to another is smoother

How to make your SQL readable so that it is enough just to skim through the code and get the general idea

It’s the SQL formatting that makes the difference

The best solution is to create a personalized style guide for SQL. The problem, but at the same time the advantage you can utilize, is that there are neither style nor formatting standards, and it’s all a matter of preference. There’s no “best” SQL formatting style, so it’s up to you and your team to create your own set of formatting rules that will be enforced to all SQL you work on. The best you can do for your SQL readability is to have the whole team always enforce the set standard – that way, everyone will be familiar with the formatting used in the company, and will be able to quickly read the code Here are some basic guidelines you can use when setting the SQL formatting rules:

• Use comments to describe what SQL does. If you’re modifying existing SQL, add the author’s name, the date, describe the modifications, and avoid questions. Don’t overdo it and comment on the obvious
• Put each major SQL statement on a new line

  USE Adventureworks2012;SELECT
  S.Businessentityid,
  E.Jobtitle
  FROM Sales.Salesperson
  WHERE S.Businessentityid &gt; 10</li>
  	<li>Put SQL keywords such as SELECT and FROM, built-in function names such as SUM, AVG, CASE, and data types such as INT, CHAR, NTEXT in the uppercase:[code]CREATE TABLE Sales.Customer(
  Customerid INT IDENTITY(1, 1) NOT NULL,
  Personid INT NULL,
  Storeid INT NULL,
  Territoryid INT NULL,
  Modifieddate DATETIME NOT NULL,
  CONSTRAINT Pk_Customer_Customerid PRIMARY KEY
  CLUSTERED(Customerid ASC)
  WITH(PAD_INDEX = OFF,
  STATISTICS_NORECOMPUTE = OFF)ON [Primary])
  
  ON [Primary]

• Use CamelCase capitalization and do not separate name parts with underscores: TableName, instead of Table_name, or use lower case and underscores to separate name parts: table_name
• Set standard abbreviations for frequently used objects, such as tbl for tables, or sp for stored procedures
• Use single quotation for characters, strings, binary and Unicode -
• Set a rule for naming aliases
• Use indenting to align wrapped long lines
• Use parentheses in complex mathematical expressions
• Be consistent with indentation – use either tab or space
• Don’t avoid using line breaks to improve readability
• Code Grouping – keep the lines that execute a certain task in separate code blocks
• Limit line length – wrap the lines longer than approximately 80 characters

And here is what you should avoid doing:

• Deeply nest the statements
• Use ambiguous names – be consistent, use simple and clear naming. Avoid excessive abbreviations
• Write a flow of execution which is difficult to follow

Once the rules are set, you need to find an easy way to enforce them. Manually applying all the rules is a time-consuming process where mistakes are very likely to happen This is where ApexSQL Refactor can help ApexSQL Refactoris a SQL Server Management Studio and Visual Studio add-in which formats and refactors SQL utilizing over 160 formatting options. It can be used to distribute and enforce SQL formatting rules among team members

1. In SQL Server Management Studio or Visual Studio’s Main menu, click ApexSQL Refactor
2. Select Formatting options
3. Set the specific option as described in the article below
4. Preview the option’s effect on the current query or a built-in example
5. In SQL Server Management Studio or Visual Studio’s Main menu, select Format SQL code to apply the formatting to SQL in the current Query Editor tab

Capitalization Before setting the capitalization standards, make sure that your database doesn’t have case-sensitive collation, as this can cause big problems when playing with capitalization. You should set the rules for:

• Reserved/key words (e.g. SELECT, DECLARE, CREATE, ALTER). Upper case is recommended
• Data types (int, nvarchar, varchar)
• Object names – identifiers (table, view and stored procedure names)
• System and built-in functions (SUBSTRING, ABS, LEFT)
• Variables

Style 1 – Keywords, types and identifiers in lowercase

create table humanresources.department(
departmentid smallint identity(1, 1) not null,
name dbo.Name not null,
groupname dbo.Name not null,
modifieddate datetime not null,
constraint pk_department_departmentid
primary key clustered(departmentid asc)
with(pad_index = off, allow_page_locks = on)on [primary])

on [primary];

Style 2 - Keywords in upper case, types in lowercase, identifiers in proper case

CREATE TABLE Humanresources.Department(
Departmentid smallint IDENTITY(1, 1) NOT NULL,
Name dbo.Name NOT NULL,
Groupname dbo.Name NOT NULL,
Modifieddate datetime NOT NULL,
CONSTRAINT Pk_Department_Departmentid
PRIMARY KEY CLUSTERED(Departmentid ASC)
WITH(PAD_INDEX = OFF, ALLOW_PAGE_LOCKS = ON)ON [Primary])

ON [Primary];

Indentation

Indenting makes SQL easier to follow, as it makes it visually structured

It’s recommended not to indent the first line in a multiple line statement, so it would be clear where the statement starts. Make sure that the SQL left margin is indented according to the section nesting

Be consistent with indenting - set up a number of spaces to use for a tab, or always use the same number of spaces. Using tabs instead of spaces is easier, as it requires less clicks, but when you start cutting and pasting your SQL, spaces are easier to handle

First, set whether to use the space or tab for indenting

ApexSQL Refactor provides multiple options for indenting arithmetic, logical and comparison operations, schema statements, data statements, and column lists. Check out the Expressions, Schema statements, Data statements, Joins, Value lists, Variables and Flow control tabs to see all available options

Style 1 - each clause begins on a new line, none is indented

select s.businessentityid, e.jobtitle
from sales.salesperson as s
inner join humanresources.employee as e on e.businessentityid =
s.businessentityid

Style 2 - each clause and each set of arguments begins on a separate line. Each statement subordinate to SELECT is also indented

SELECT
S.Businessentityid, E.Jobtitle
FROM
Sales.Salesperson
AS S
INNER JOIN
Humanresources.Employee
AS E
ON E.Businessentityid = S.Businessentityid;

SELECT
S.Businessentityid,
E.Jobtitle,
S.Salesquota,
S.Salesytd,
S.Saleslastyear
FROM
Sales.Salesperson
AS S
INNER JOIN
Humanresources.Employee
AS E
ON E.Businessentityid = S.Businessentityid;

CREATE TABLE Humanresources.Department (
Departmentid SMALLINT IDENTITY ( 1, 1 )
NOT NULL,
Name dbo.Name NOT NULL,
Groupname dbo.Name NOT NULL,
Modifieddate DATETIME NOT NULL,
CONSTRAINT Pk_Department_Departmentid
PRIMARY KEY CLUSTERED ( Departmentid ASC )
WITH ( PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON ) ON [Primary] )

ON [Primary];

CREATE TABLE Humanresources.Department (
Departmentid SMALLINT IDENTITY ( 1, 1 )
NOT NULL,
Name dbo.Name NOT NULL,
Groupname dbo.Name NOT NULL,
Modifieddate DATETIME NOT NULL,
CONSTRAINT Pk_Department_Departmentid
PRIMARY KEY CLUSTERED ( Departmentid ASC )
WITH ( PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON )

ON [Primary];

Style3 - line breaks after both opening and closing parentheses

CREATE TABLE Humanresources.Department
(
Departmentid SMALLINT IDENTITY
(
1, 1
) NOT NULL,
Name dbo.Name NOT NULL,
Groupname dbo.Name NOT NULL,
Modifieddate DATETIME NOT NULL,
CONSTRAINT Pk_Department_Departmentid
PRIMARY KEY CLUSTERED
(
Departmentid ASC
)
WITH
(
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
)
) ON [Primary]

Comments

Comment types that can be used in SQL are:

• Block comments /*…*/
• Inline comments --

Here are a couple of guidelines for comments you should follow:

• Write comments clearly and consistently
• Don’t add too many comments
• Check whether or not you really need a developer’s name and revision history

-- Select top sales agents
SELECT
S.Businessentityid,
E.Jobtitle,
S.Salesquota,
S.Salesytd,
S.Saleslastyear
FROM Sales.Salesperson
WHERE S.Businessentityid > 10
ORDER BY
S.Salesquota;
/*add them to the bonus table*/

Using ApexSQL Refactor, one type of comment can be changed to another one

/* Select top sales agents*/
SELECT
S.Businessentityid,
E.Jobtitle,
S.Salesquota,
S.Salesytd,
S.Saleslastyear
FROM Sales.Salesperson
WHERE S.Businessentityid > 10
ORDER BY
S.Salesquota;
/*add them to the bonus table*/

Creating a SQL formatting standard usually takes a lot of testing and tweaking. Once you are satisfied with the rules, save them to a formatting profile and distribute to all team members

1. In the Formatting options dialog, click Export to save the formatting rules to an XML file.
2. Specify the file name and location
3. Copy the XML file to a team mate’s machine
4. In the Formatting options dialog, click Import
5. Navigate to the XML file

Now your colleagues will have the same formatting as you do

With SQL, its readability is as important as the fact that it can be executed. Clear formatting guidelines and automatic implementation of formatting rules will ensure that all team members and anyone who inherits the code can read it easily. Use ApexSQL Refactor to automatically implement all SQL formatting rules