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


Coding Standards - Part 1


Coding Standards - Part 1

Author
Message
tjaybelt
tjaybelt
SSC Eights!
SSC Eights! (977 reputation)SSC Eights! (977 reputation)SSC Eights! (977 reputation)SSC Eights! (977 reputation)SSC Eights! (977 reputation)SSC Eights! (977 reputation)SSC Eights! (977 reputation)SSC Eights! (977 reputation)

Group: General Forum Members
Points: 977 Visits: 470
I love having standards spelled out. Makes everyones job that much easier, because its readable and, well, standard.

Personally, I like naming indexes differently. I will put the index type at the beginning, so PK_TableName_FieldsInIndex. This way, I know at the beginning what type of index it is, then the table it is applied to, and the fields in that index. I do this because I've seen errors in the application that just spit out the name of the index, or foreign key or other constraint. The error want handled well, so if the name is readable, one can discern the problem easily. But it also helps me to see what indexes are there or not there, based on the name.

For triggers and stored procs, i name them trTrigger and spProcName. Only when I want a proc to be accessible via master do I name it sp_ProcName, and thats usually a helper proc for me the dba, and not a application specific one anyway. So sticking it into master will hide it from the customer, should they ever go alooking.

One final thing i would like to note, is that all sql code that I write, I use templates to start them, and save the files to disk every time. We use source safe, so i usually aadd them into that, to version control them. As I am testing a proc, for example, I will add in testing code below the actual proc create code, but within comments. So anytime I want to reload the proc, simply open up the script file, and execute it. It will drop the proc, then readd it. The testing code is in comments, but I keep that for reference. Oft times I will even record the time of execution and number of records retrieved, so at a later date, I can verify the integrity of the proc and its resulting data, compared to earlier trials.

Anyway, thats some more of what i do to maintain a clutter free dba life.



Eve
Eve
Mr or Mrs. 500
Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)

Group: General Forum Members
Points: 522 Visits: 2
I wish we had standards like that. We had some default standards that came with packaged application and we have to follow them, but quite a few times we wish for something more discriptive, like in the article.



Andy Warren
Andy Warren
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: Moderators
Points: 25757 Visits: 2747
I avoid underscores and prefer mixed case for object names, easier to read in my opinion. The thing about underscores is if you dont use them consistently, it makes it that much hard to type sql on the fly...was it customer_info or CustomerInfo? The other thing, though minor, is that underscores are wildcards when you use a like.


Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
michael.rosqvist
michael.rosqvist
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 1
I am curious about why the clustering should be obvious from the name of the index?

Clustered Index - IDX
Nonclustered Index - NDX

Isnt it a bit awkward to have to change the name of the index when you change a clustered index to a nonclustered?

The different standards I have used denotes if the index is on a foreign key or not. If not it is just numbered.



rsudhi
rsudhi
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
Good article. I like this one 'cause I am also preparing one for the past one month!
But, why can't we have the naming standards for Triggers, udfs etc same like views. i.e by prefixing the characters on the nam of the object. so that we can maintain the consistency all over our database ojbects.



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

Group: Administrators
Points: 148841 Visits: 19446
Views tend to be across tables, so a prefix doesn't matter. Personally, I like having the names of the objects associated with the table, next to the table in an object list. Easier for me to find them.

I don't often change from a non-clustered to a clustered index. Having IDX means I can easily find the clustered index in a group, and it isn't usually the PK.

Steve Jones
steve@dkranch.net

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
danpep
danpep
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 50
Thanks for covering an often overlooked subject (by developers without team leaders anyway).

The choice of convention is, well, a matter of choice. The main objective being consistency of names.

I do have one suggestion to add regarding naming of stored procedures:

I like the object.method convention used by c/VB/other programmers. I try to follow this convention since the SPs also form part of an application's code. The names are a bit subjective, but I think it makes things quite clear. Here are some examples:

Company_Add, Company_Delete, Customer_Get, Customer_GetOrders, etc. When to use CustomerOrder_Get or Customer_GetOrders is the common ambiguity encountered. Sometimes the parameter(s) passed in will determine the best name, e.g. CustomerOrder_Get @Customer, @OrderNum vs. Customer_GetOrder @Customer. If anyone has any thoughts on this conflict, I'd really like to hear them.

This convention also groups the procedures for any given table/entity together, i.e. all Company related functions are listed together in Enterprise Manager/Access/Query Analyser.

I'm not a huge fan of the "sp_" prefix practice, becasue it means I can't just hit "C" on the keyboard to go to procs called "Company_...". Also I don't see that the prefix adds any real value/clarity.

Thanks again for raising the topic.



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

Group: Administrators
Points: 148841 Visits: 19446
Not a bad idea. One I hadn't thought of, especially since most databases only have a single target app. Might be easier to find them that way, though they'd be mixed with my Customer table, the CustomerAddress, CustomerType, etc.

Steve Jones
steve@dkranch.net

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
madhutv
madhutv
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
[Interesting..
But missing standards for procedures, which also has equal or more importance.
Madhu V Pillai
]
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/codingstandardspart1.asp
[/quote]



Andy Warren
Andy Warren
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: Moderators
Points: 25757 Visits: 2747
Tend to agree about the proc names - thats the same reason I DONT use hungarian for property/method names in objects.


Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
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