SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Coding Standards Part 2 - Formatting


Coding Standards Part 2 - Formatting

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)

Group: Administrators
Points: 327346 Visits: 20085
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/codingstandardspart2formatting.asp

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
AdamCogan
AdamCogan
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 6
Great article, we follow the naming as per below. These cover some of the same but also more areas of SQL Server naming conventions.
http://www.ssw.com.au/ssw/standards/DeveloperSQLServer/SQLServerStandard_1_ObjectNaming.aspx

http://www.ssw.com.au/ssw/standards/DeveloperSQLServer/SQLServerStandard_2_StoredProcedureNaming.aspx

http://www.ssw.com.au/ssw/standards/DeveloperSQLServer/SQLServerStandard_3_StoredProcedureFormatting.aspx

http://www.ssw.com.au/ssw/standards/DeveloperSQLServer/SQLServerStandard_4_IndexesNaming.aspx

Adam



Andy Warren
Andy Warren
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: Moderators
Points: 52078 Visits: 2796
Good article. In particular the point about putting spaces in so that you can jump from word to word easily is one often overlooked.

Andy
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Dave Poole
Dave Poole
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34898 Visits: 3729
Yes, good ideas here.

Anything that makes old code easy to read gets my vote.

Has anyone used sp_addextendedproperty to help comment objects within your databases?

LinkedIn Profile
www.simple-talk.com
Nick Beagley
Nick Beagley
SSC Eights!
SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)

Group: General Forum Members
Points: 825 Visits: 266
Steve, Agreed with everything you said. I'm particularly fond of commas preceeding column names.

Adam, Thanks for your guidelines.



Dennis Raher
Dennis Raher
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 1
Steve,

I totally agree with your comments and I personally use a style very similar to yours, and for the same reasons. We do not actually have a standard yet, but we're just in the process of looking at it now, so your article is timed perfectly.



Steve Jones
Steve Jones
SSC Guru
SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)SSC Guru (327K reputation)

Group: Administrators
Points: 327346 Visits: 20085
Thanks for all the comments. This is something that I have found helpful as well and hopefully others will adopt something similar to standardize their environments.

Steve Jones
sjones@sqlservercentral.com
http://www.sqlservercentral.com/columnists/sjones

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Kristin Echols
Kristin Echols
SSC-Enthusiastic
SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)SSC-Enthusiastic (194 reputation)

Group: General Forum Members
Points: 194 Visits: 84
I like the trick of putting the commas before the field name. Looked weird at first glance, but made perfect sense when explained. I will pass this article on to my co-developers; we already used Part 1 of the article to develop our naming standards.

David Poole asked about using sp_addextendedproperty to comment database objects. That is our standard for database documentation. I wrote an ASP app that allows the developer to select a database and view the extended properties for tables, columns, views, etc. Eventually, we want to extend the app to include an extended property for each system that uses a particular object. Our developers are more likely to document if it doesn't involve a word processor!



jodiem
jodiem
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 1
Steve, thanks for the great article, it will create a lot of discussion. Here's my 2c worth.

All of these rules are GREAT if you use a text editor environment. But not so great if you use GUI. I personally always use GUI where possible. I am not able to understand a 50 line long SQL statement with joins on 10 tables without being able to see it graphically. Where these formatting ideas fall down in a GUI enviroment are:
As soon as you view the code in A GUI enviromnent all your formatting disappears.
Tabs don't appear the same way in text and GUI enviromnent so what looks great and all lined up in query analyzer is an undreadable mess in Access.
You don't need aliases when you create a query in a GUI environment, because it fully qualifies the names for you automatically, and I personally find it harder to read aliases than fully qualified names.
Basically the point is, unless your whole team now and in the future is going to use exclusively text editors, then you need to be flexible with a coding standard like this, because to re-format a large query created in a GUI enviroment just becuase the people who love to type can't read it, is a waste of time.
The Answer? I beleive the answer lies in an automatic formatter - does anyone know of one? QUEST software has a fantastic tool for Oracle called Formatter Plus. I've asked them if they're doing one for SQL but they said not at this stage. Maybe if everyone can send them an email, they might get something happening.



Simon Sutcliffe
Simon Sutcliffe
SSC Veteran
SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)SSC Veteran (277 reputation)

Group: General Forum Members
Points: 277 Visits: 6
If you have to be a ludite and use a text editor to develop SQL, rather than as suggested by another responder - use GUI it's easier - then it would also be advisable to make a distinction between table and column names and SQL RESERVED WORDS. The best way I have seen so far is :-

SELECT Column_Name_1
,Column_Name_2
,MAX(Column_Name_3)
FROM tbl_Name
WHERE Column_Name_1 > 0
AND Column_Name_2 > 0 .....etc.....
GROUP BY Column_Name_1
,Column_Name_2

Not sure how this will appear in the forum but it looks fine in a fixed spaced editor.

By using UPPERCASE for the reserved words it makes their importance stand out. and much easier to read.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search