Leading Comma or Trailing Comma?

Question

I'm writting a SQL Coding Standard for the company where I work.

I've read literature that says that leading is the best option

select a.name
      ,a.surname
      ,a.address
from person a 

But also trailing:

select a.name,
       a.surname,
       a.address
from person a 

or non standard at all

select name, is_disabled,
is_policy_checked, is_expiration_checked
from sys.sql_logins;

My first question, is there any SQL Coding Standard worldwide accepted. If not, why not? I think this would be incredible usefull. Something like PEP8.

And which would be the most practical way to solve this problem?

Thanks in advance

Answer 1

If a query is parsed and executed without any errors. it is means it met the basic standards of that language.

As far as where to put commas and how to indent your code its all down to personal preferences.

Yes there are some best practices defined by the people which are again considered by most of the people as a "Best Practice" but not all.

There are some best practices on which most of the people will agree but not all.

1. Things are clever, but not too clever (KISS / Keep it simple stupid)
   

2. A Parsed query is not the best query, try writing

Covering Queries when ever possible.

3. Use atleast two-part name for your object like [Schema].[TableName]
   

4. Object (Tables, Columns, Stored Procedures) variables and functions are well named and make sense without having to think too much.
   

5. You come back to it after a weekend off, and you can jump straight in
   

6. Things that will be reused are reusable
   

7. Code that looks cleaner. will be easier to debug and modify at later stage.
   

8. "Comments" using comments to explain what a code snippet does, so if you come back to it after a month of so you will know by just reading the comments what the code does rather then actually going through the code itself.
   

9. Use CAPITALS for key words like " SELECT, FROM , WHERE " --<--

Keeping all these things in mind for above query I would write it something like this.

SELECT  A.NAME
       ,A.SURNAME
       ,A.ADDRESS

FROM   PERSON A

Coming back to the "Important Question" Leading Comma or Trailing Comma?

I personally find it easier to have leading commas in my code, as it makes easier for me to find where the the next column starts from , or to find the missing commas.

for example in the following query my answer to one of the question on stack over flow

SELECT     radius
         , Diameter
         , CASE WHEN POWER( @p1 - x, 2) + POWER( @p2 - y, 2) <= POWER(radius, 2)
             THEN 'Inside The Circle'
            WHEN POWER( @p1 - x, 2) + POWER( @p2 - y, 2) > POWER(radius, 2)
             THEN 'Outside the Circle' END   [Inside/Outside]
FROM @t

The leading comma makes it so much easier to find a missing comma if I had one and to tell exactly how many column there are this this SELECT query.

Some Useful Link

Aaron Bertrand - Best Practices for Stored Procedures

Richard Espinoza SQL Server Concepts and Best Practices to Build Transact SQL Stored Procedures

pinaldave SQL SERVER – Bad Practice of Using Keywords as an Object Name – Avoid Using Keywords as an Object

Answer 2

There is no single standard for how to write SQL code. Some use upper case keywords, some lower case. Some use trailing commas, some use leading commas, some put all the values on the same line.

In normal text you use trailing commas, so that is easier to read for most people. One advantage of using leading commas is that the last item doesn't have a different format from the others, but on the other hand the first item gets a different format.

The practical way to solve this is to pick a reasonable standard in the organisation that you belong to, and stick to it. It's more useful to have code with a consistent look, than to try to find the format with the most advantages and least disadvantages.

Just as an example, this is the format that I have come to use, after years of trying different variations:

select
    p.Name,
    p.Surname,
    a.Street,
    a.Zip,
    a.City
from
    Person p
    inner join Address a on a.AdressId = p.AddressId
where
    p.PersonId = 42