SQL Formatting standards – Capitalization, Indentation, Comments, Parenthesis

SQL-Tables
SQL-Tables

SQL Formatting standards – Capitalization, Indentation, Comments, Parenthesis

Welcome to CodeSpread!

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

SQL indentation options

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

Formatting options for expressions

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;

Style 3 – indent all list items equally, together with all columns and table names

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;

Parentheses

Parentheses can be used in different contexts – in SELECT statements, function parameters, DDL statements, mathematical expressions, etc.

The opening parenthesis in an expression can be placed on a new line and followed by an immediate line break. The closing parenthesis can also be placed on a new line, with or without a line break afterwards. Use whatever makes your code more readable

General formatting options

Style1 – no line breaks after opening or 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] )

ON [Primary];

Style2 – a line break after the opening parenthesis

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

ApexSQL Refactor comments formatting options

-- 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

Author: hershey

A passion for knowledge drives me to do programming, A passion for programming drives me to create something different, A passion for creation drives me to spread the knowledge.

Share This Post On

0 Comments

  1. nice superb explaination

    Post a Reply
  2. thanks for the tutorial
    I am having a problem.
    The feature “Create SQL server database” isn’t available , I can’t select it.
    Can you help, please ?

    Post a Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

More from CodeSpread:

  • Styles of Source Code Comments in C#, SQL, XML, HTML, CSS, JavaScriptStyles of Source Code Comments in C#, SQL, XML, HTML, CSS, JavaScriptDevelopers take months to write beautiful pieces of code and reviewer gets only few hours or I say, minutes to provide suggestions and improvisations. Then it becomes a hard deal for a developer to...
  • VB.Net: Download file from FTP and UnZipVB.Net: Download file from FTP and UnZipExperience: Many application requires data migration tasks to execute on daily basis. To achieve this we generally seek help of SSIS package, if at all we are making use of SQL sever. ...
  • SQL Bitwise operator can simplify DB designSQL Bitwise operator can simplify DB designFrankly speaking, I never tried SQL Bitwise operator but while doing DB design today for one of the projects, I came to know about this operator from one of my friend and I really liked it. To expl...
  • IDENTITY as SQL Property and IDENTITY as SQL FunctionIDENTITY as SQL Property and IDENTITY as SQL FunctionIDENTITY as SQL Property: Almost all the time, We have a table with a primary key and would like to insert a new value automatically in the primary key field. To achieve this, We have IDENTITY ...
  • SQL: Story of IndexesSQL: Story of IndexesI know many people know everything about this topic but as this is one of my favorite topics, I will make sure that I will give you some additional information or knowledge. My first thought abo...
  • Few lines about SQL Use statementFew lines about SQL Use statement We see this statement in Management Studio now and then and it is used quite extensively. For Example, [code] USE MyDatabaseName GO [/code] MyDatabaseName : MyDatabaseName is the n...
  • SQL fry: PIVOT and UNPIVOTSQL fry: PIVOT and UNPIVOTWhat is PIVOT? In our day-to-day SQL server coding, PIVOT and UNPIVOT are not very familiar words but still holds good if we understand them correctly and know when to use it.Web Definition of PIV...
  • Transact-SQL : Try-Catch BlockTransact-SQL : Try-Catch BlockSimilar to exception-handling features of .Net, T-SQL code provides a TRY-CATCH block. It is one of the implicit features of T-SQL and a famous error handling technique to catch errors. A TRY-CA...