Rules of SQL formatting – Commas and spacing in T-SQL

This article describes some possibilities of formatting commas and spacing in T-SQL using ApexSQL Refactor SQL formatter with nearly 200 SQL formatting options.

Formatting commas in T-SQL

Commas in T-SQL are used to format numbers, as list-separators, and value separators. When commas are used as separators, there is no specific rule for placing commas at the end of a line or at the beginning.

Some opinions are based on the fact that English and European languages are read from left to right and that the natural position of the comma separator in programming languages is at the end of a line.

“Put commas at the end of a line, not the start. A comma, semicolon, question mark, or periods are visual signals that something has just ended, not that it is starting. Having a comma at the start of a line will make the eye tick leftward as it looks for that missing word that was expected before the comma.” [1]

SELECT
       e.BusinessEntityID, 
       p.Title, 
       p.FirstName, 
       p.MiddleName, 
       p.LastName, 
       p.Suffix, 
       s.Name AS Shift, 
       d.Name AS Department, 
       d.GroupName, 
       edh.StartDate, 
       edh.EndDate

However, most often commas as list-separators are written at the beginning of a line to make commenting of list members easier in development. This is also a standard in Adventureworks2012 database:

SELECT
     e.BusinessEntityID
   , p.Title
   , p.FirstName 
   , p.MiddleName
   , p.LastName -- easier commenting of list members  
   , p.Suffix
   , s.Name AS Shift
   , d.Name AS Department
   , d.GroupName
   , edh.StartDate 
   , edh.EndDate;

Both options for formatting commas in column lists are available in ApexSQL Refactor in the Lists tab under the Columns sub-tab, of the Column lists option section:

Also, formatting commas in value lists has supporters on both sides. Some will place commas at the beginning of the line to make commenting values easier:

     VALUES
           ('DepartmentName'
            ,'GroupName'
            ,GetDate())

The others will place commas at the end of a line to follow English and European linguistic conventions:

     VALUES 
           ('DepartmentName',
            'GroupName',
             GetDate())

There is also an option to format commas in value lists, in the Options window in the Lists tab under the Values sub-tab:

Format spacing in T-SQL

Using ApexSQL Refactor better T-SQL readability can be achieved by using several spacing options in the Formatting, Assignments, Arithmetic and Comparison tabs of the Options window and a proper indentation.

1. To achieve readability with spaces after commas choose the Add spaces after commas option in the Formatting tab from the Options dialog:
   

   Before:

   INSERT INTO HumanResources.Shift(Name,StartTime,EndTime,ModifiedDate)

   After:

   INSERT INTO HumanResources.Shift(Name, StartTime, EndTime, ModifiedDate)

2. Using the Add spaces around operators option in the Comparison tab:
   

   Before:

   WHERE b.[ProductAssemblyID]=@StartProductID 
               AND @CheckDate>=b.[StartDate] 
               AND @CheckDate<=ISNULL(b.[EndDate], @CheckDate)

   After:

   WHERE b.[ProductAssemblyID] = @StartProductID 
               AND @CheckDate >= b.[StartDate] 
               AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)

3. Using the Add spaces around operators option in the Arithmetic tab:
   

   Before:

   SELECT (quantity*2) FROM products WHERE order_number = 10;
   SELECT (quantity*5) FROM products WHERE order_number = 10;

   After:

   SELECT (quantity * 2) FROM products WHERE order_number = 10;
   SELECT (quantity * 5) FROM products WHERE order_number = 10;

4. Using the Add spaces around assignment operators option in the Formatting tab:

   “The equal sign (=) is the only Transact-SQL assignment operator.” [2]

   

   Before:

   SELECT(quantity * 2) FROM products WHERE order_number=10;
   SELECT(quantity * 5) FROM products WHERE order_number=10;

   After:

   SELECT(quantity * 2) FROM products WHERE order_number = 10;
   SELECT(quantity * 5) FROM products WHERE order_number = 10;

5. And adding spaces inside and outside parentheses:
   

   Before:

   SELECT(quantity * 2) FROM products WHERE order_number = 10;
   SELECT(quantity * 5) FROM products WHERE order_number = 10;

   After:

   SELECT ( quantity * 2 ) FROM products WHERE order_number = 10;
   SELECT ( quantity * 5 ) FROM products WHERE order_number = 10;

References:

[1] Joe Celko’s SQL Programming Style
[2] Assignment Operator (Transact-SQL)

Useful resources:

Books Online for SQL Server – Arithmetic Operators
Books Online for SQL Server – Comparison Operators (Transact -_SQL)