Rules of SQL formatting – Nesting and indenting SQL statements

Applies to
ApexSQL Refactor

Summary

This article explains nesting and indenting of SQL statements as another aspect of SQL formatting style.

Description

For nesting and indenting SQL statements which are another aspect of SQL formatting style, ApexSQL Refactor can be used, a SQL formatter with nearly 200 formatting options.

In ApexSQL Refactor there are a few options to set indenting rules in project. Under the ApexSQL Refactor menu in the Formatting tab, set general indention rule for SQL statements. The Indent using spaces option indent operations inserting space characters. If the Indent using tabs option is chosen indent operation will insert for each tab character the number of spaces specified in Tabs equal box. The Smart indent option aligns identifiers in the data statements (Select, Update, Delete, Insert).

ApexSQL Refactor also has options for nesting and indenting expressions, schema, and data statements, flow statements, variables and JOIN statements.

Nested query or sub-query is a query within a query usually embedded in a WHERE clause, and are alternative way of returning data from multiple tables.

Nesting and indenting expressions

Nesting can be used along with comparison, arithmetic, and logical operators. Formatting expressions in SQL code can be managed under the Expressions tab in the Options window.
Formatting arithmetic operations such as addition, subtraction, multiplication, division and modulo can be set by moving the operation on a new line and setting the custom number of spaces for indention:

In this way, readability of the arithmetic operators can be improved:

Before:

SELECT employee,
       oldsalary,
       newsalary
FROM salary
WHERE (((oldsalary <= 5)
	 AND oldsalary + @COLI * oldsalary < 10 )
       OR (((@COLI + 10) - 45 + 125) / 5) < 1000);

After:

SELECT employee,
       oldsalary,,
       newsalary
FROM   salary
WHERE   (((oldsalary <= 5)
	 AND 
       oldsalary + @COLI * oldsalary < 10)
	 OR (
       (
       (@COLI + 10) - 45 + 125) / 5) < 1000);

Same formatting can be applied to comparison and logical operations in SQL code:

After formatting:

SELECT employee,
       oldsalary,
       newsalary
FROM salary
WHERE (((oldsalary <= 5)
        AND 
      oldsalary + @COLI * oldsalary 
      <
      10 )
        OR 
      (((@COLI + 10) - 45 + 125) / 5) 
      <
      1000 );

After formatting:

SELECT employee
	,oldsalary
	,newsalary
FROM salary
WHERE (
		(
	        (oldsalary <= 5)
		AND oldsalary + @COLI * oldsalary < 10)
		OR (((@COLI + 10) - 45 + 125) / 5) < 1000 );

Nesting and indenting SQL statements

In order to improve readability of nested SQL statements ApexSQL Refactor has multiple options for formatting statements. Using the Place on a new line option, and custom indenting, readability of stored procedures, functions, etc., can be achieved:

For example:

CREATE TABLE Person 
(
	FirstName VARCHAR(50),
	LastName VARCHAR(50)
	,Age INT
);
GO

CREATE PROCEDURE HumanResources.uspUpdateEmployeeLogin @BusinessEntityID [INT]
	,@OrganizationNode [HIERARCHYID]
	,@LoginID [NVARCHAR] (256)
	,@JobTitle [NVARCHAR] (50)
	,@HireDate [DATETIME]
	,@CurrentFlag [dbo].[Flag]
AS
	UPDATE [HumanResources].[Employee]
	  SET 
            [OrganizationNode] = @OrganizationNode
	WHERE [BusinessEntityID] = @BusinessEntityID;

Nesting and indenting SQL data statements

Nested statements are usually Select statements. In ApexSQL Refactor in the Lists tab under the Columns sub-tab, formatting options can be combined for data statements formatting such as Select, Insert etc.:

Before formatting:

SELECT DISTINCT c.LastName a , 
		c.FirstName b

After formatting, indent for 0 spaces:

SELECT DISTINCT
c.LastName a , 
c.FirstName b

After formatting, indent for 6 spaces:

SELECT DISTINCT
      c.LastName a , 
      c.FirstName b

Further formatting of data statements can be achieved using the Move FROM clause to new line option and the Move WHERE clause to new line option, and custom indenting under Statements tab:

Example:

INSERT INTO #tmp
      SELECT DISTINCT 
             c.LastName a,
	     c.FirstName b
        FROM Person.Person AS c
                    JOIN HumanResources.Employee e ON e.BusinessEntityID = c.BusinessEntityID
      WHERE c.BusinessEntityID IN 
      (
		SELECT SalesPersonID
		  FROM Sales.SalesOrderHeader
		WHERE SalesOrderID IN (
				SELECT SalesOrderID
				FROM Sales.SalesOrderDetail
				WHERE ProductID IN 
                                (
						SELECT ProductID
						  FROM Production.Product AS p
						WHERE ProductNumber IN (1, 2, 3)
				)
		)
        );

Nested Select statements are most frequently used nested statements:

Using the Move code within parenthesis to a new line option, the SELECT statement can be indented by custom number of space:

WHERE c.BusinessEntityID IN 
              (
		SELECT SalesPersonID
		FROM Sales.SalesOrderHeader
		WHERE SalesOrderID IN 
                        (
				SELECT SalesOrderID
				FROM Sales.SalesOrderDetail
				WHERE ProductID IN 
                                         (
						SELECT ProductID
						FROM Production.Product AS p
						WHERE ProductNumber IN (1 ,2, 3)
					)
				)
		);

Nesting and indenting JOIN statements

To achieve readability of complex JOIN statements the JOIN and ON keywords can be placed, as well as nested JOIN operations, on a separate line and indent for a custom number of spaces:

Example:

SELECT Tunes.NAME,
      Musicians.NAME,
      Duos.NAME
FROM 
      Tunes
            INNER JOIN 
                  Musicians 
            ON Tunes.NAME = Musicians.NAME
            INNER JOIN 
                  Duos 
            ON Musicians.NAME = Duos.NAME;

Nesting and indenting value lists

In the Lists tab under the Values sub-tab, additional formatting options can be found for data statements:

Before:

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

After formatting without indenting:

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

After formatting with indent:

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

Variables nesting and indenting

To improve data and schema statements, the variables formatting options can be used further under the Variables sub-tab, in the Lists tab:

Before:

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

After formatting, indent for 0 spaces:

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

After formatting, indent for 8 spaces:

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

Flow control nesting and indenting

Flow statements formatting options include formatting options: Place WHEN on a new line, Place THEN on a new line, and Place ELSE on a new line:

Here is the example of these options with custom indenting:

'Price Range' = CASE
		    WHEN listprice=0
		       THEN 'Mfg item - not for resale'
		    WHEN listprice<50
		       THEN 'Under $50'
			  ELSE 'Over $1000'
		 END

References

Books Online for SQL Server – Manage code Formatting
Books Online for SQL Server – Arithmetic Operators (Transact-SQL)
Books Online for SQL Server – Comparison Operators (Transact-SQL)