Coding Standards - Part 1

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/codingstandardspart1.asp

  • Good article - it's interesting to see someone elses naming convention. Thanks Steve.

    What about DTS packages and jobs? Does anyone have any naming conventions for those? We're currently reviewing ours and appreciate some ideas.

    Thanks, Ryan.

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • Coding standards are great but seems you always have someone who just plain ignores them. Which is a shame.

    As for DTS I suggest you make a name that will organize properly but a convention is not really a true neccessity here as you can include a package description. However the name should be a logical short version of the description or make a certain sense in helping quickly ID it. I usually use DBRelatedTO_ShortDescrip such as

    PUBS_ExportAuthToXlsOnWeb

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • Not bad! We don't have one much of one here. While senior people may rebel a bit, junior developers LIKE having a standard to follow. Not only that, they tend to survive by cloning code frequently, so if you have a a bunch of different styles in use already, it just gets worse!

    One thing I'd add, no spaces in database names either. If MS would PLEASE add a server configuration setting that will let disallow spaces in object names totally, that would SO useful.

    Andy

  • Interesting, I liked the naming of the triggers.

    Cheers, Joe.

  • Hey,

    Great article. I was wondering how far out I would be with my naming conventions, but fortunately, I use pretty much all of those. Only ones I had not thought of was Triggers and Defaults!

    With regards to DTS, I do try and adhere to a standard. All DTS Package names should give an indication as to the task. For example: JDE Inventory Download. For the description field I use the Database Name. Just found it easier that I could group the DTS's to see which data goes in which DB. As with all objects within the DTS, I always change their default description. For example, if I inserted an "Execute SQL Task", I'd change alter the Description to make it more meaningfull. But again, all that is documented!

    One other standard which I just started to make my SP's meaningfull. I use <Application>_<SQL Statement>_<SP Name>. For example: SHOPFLOOR_INSERT_NEWORDER

    And very much agree with Antares. Standards are great...however, trying to make others accept and adhere to your standards is another issue..!!

    Clive Strong

    clivestrong@btinternet.com

  • Nice Article. Our standards are similar but we include a further descriptive prefix to tables, views and procedures to enhance readablility in any consolidated documentation for projects.

    These include a single letter identifiying the object type ( 't' for tables, 'v' for views, 'p' for procs) and a single letter for basic function ( 'l' for lookup, 's' for static, 't' for transaction, 'p' for procedural ).

  • Thanks for the comments. As far as DTS pacakges go, I haven't done enough work in a multi-DBA environment to come up with a good one. I have done the following:

    DBAxxxxx - DBA related jobs (backup, etc.) with the xxx being a description.

    ExtractXXXXX - Extraction procedures for

    processes that I use to move data out of the database and send it somewhere else.

    Generate XXXX - DTS packages that generate data that is sent to a person, like a report.

    That's about as far as I've gotten. I like Antares idea for DTS packages.

    as far as adherence. I've never had an issue with going to a manager and asking for adherence. Then I get them to lean on the others.

    Steve Jones

    steve@dkranch.net

  • Good article. (Was that an echo?) It is always worth reading up on how other people do, let's call it "non-standardized" things, such as naming and coding conventions. Eventually, they become widespread standards, or so one can only hope.

    A quick thought or two: for indexes and the like, instead of tagging them with a number we put the column actually indexed at the end (such as PK_Table_ColumnName). For compound indexes, either we do multiple columns, a brief description, or (if too long) an arbitrary number (1, 2, etc.) All this makes it easier to get a list of indexes for a table and see what's covered and what's not.

    I'll toss in our foreign key naming conventions: FK_<child table constraint is "owned" by>_<parent table constraint references>. Having consistent names on the "hidden" database objects (indexes, defaults, constraints, triggers) has made management a LOT easier the few times it's really mattered.

    Steve, just from idle curiosity, and with no intent to provoke lengthy debate on points of religious dogma, are there an particular reasons why do you avoid underscores and all-upper case words? We mix and match in what I like to think of as a judicious fashion. When reading code or reports, quick visual identification of disparate objects can be very useful.

    Philip

  • I do agree that quick identification can be usefu. One reason why I put the tablename first is so all items relating to the table are close together.

    As far as underscores go. No real reason other than I had to pick something and tend to prefer UsingProperCaseNames to Using_Proper_Case_Names. Shorter, plus this is what MS decided to recommend for the .NET stuff. I always disliked the all CAPS stuff in C for constants. I know they stand out, but I've never had an issue running through code and checking variables. If I'm confused or new, I should be extra careful anyway before changing the code. Plus it's easier for me to just be consistent than to try and figure out if I should be making something all CAPS.

    Not against it and if I worked in your shop, I'd adapt. Just what I decided was easier.

    Steve Jones

    steve@dkranch.net

  • 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.

  • 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.

  • 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

  • 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.

  • 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.

Viewing 15 posts - 1 through 15 (of 50 total)

You must be logged in to reply to this topic. Login to reply