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 (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61905 Visits: 19099
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
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 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
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11435 Visits: 2730
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
David.Poole
David.Poole
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7478 Visits: 3282
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

Newbie on www.simple-talk.com
Nick Beagley
Nick Beagley
SSChasing Mays
SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)

Group: General Forum Members
Points: 609 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
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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 (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61905 Visits: 19099
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 (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 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 Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 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 Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 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