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
Leading comma advocates typically argue that leading commas are better because you can easily comment out lines in a query without having to edit other lines. With trailing commas, that edit is necessary to prevent errors and, they say, easy to forget.
Trailing commas (sometimes called "final commas") can be useful when adding new elements, parameters, or properties to JavaScript code. If you want to add a new property, you can add a new line without modifying the previously last line if that line already uses a trailing comma.
A trailing comma, also known as a dangling or terminal comma, is a comma symbol that is typed after the last item of a list of elements. Since the introduction of the JavaScript language, trailing commas have been legal in array literals. Later, object literals joined arrays.
Description: In Python, a tuple is actually created by the comma symbol, not by the parentheses. Unfortunately, one can actually create a tuple by misplacing a trailing comma, which can lead to potential weird bugs in your code. You should always use parentheses explicitly for creating a tuple.
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.
- Things are clever, but not too clever (KISS / Keep it simple stupid)
Covering Queries when ever possible.
- Use atleast two-part name for your object like [Schema].[TableName]
- Object (Tables, Columns, Stored Procedures) variables and functions are well named and make sense without having to think too much.
- You come back to it after a weekend off, and you can jump straight in
- Things that will be reused are reusable
- Code that looks cleaner. will be easier to debug and modify at later stage.
- "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.
- 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With