Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Coding Standards - Part 1 Expand / Collapse
Author
Message
Posted Thursday, May 9, 2002 1:15 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 4:44 PM
Points: 615, Visits: 443
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.




Post #33059
Posted Thursday, May 9, 2002 2:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 27, 2007 11:29 AM
Points: 132, 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.




Post #33060
Posted Thursday, May 9, 2002 2:13 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: 2 days ago @ 2:51 PM
Points: 6,790, Visits: 1,902
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
Post #33061
Posted Monday, May 13, 2002 12:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 28, 2005 1:01 AM
Points: 17, 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.






Post #33062
Posted Monday, May 13, 2002 10:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 13, 2002 12:00 AM
Points: 1, 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.




Post #33063
Posted Monday, May 13, 2002 5:37 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 12:34 PM
Points: 31,181, Visits: 15,626
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
Post #33064
Posted Tuesday, May 14, 2002 8:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 18, 2013 3:26 AM
Points: 10, Visits: 37
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.




Post #33065
Posted Tuesday, May 14, 2002 9:27 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 12:34 PM
Points: 31,181, Visits: 15,626
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
Post #33066
Posted Monday, May 20, 2002 3:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 1, 2005 1:48 AM
Points: 1, 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]




Post #33067
Posted Monday, May 20, 2002 5:06 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: 2 days ago @ 2:51 PM
Points: 6,790, Visits: 1,902
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
Post #33068
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse