Database Commenting Guideline

  • Comments posted to this topic are about the content posted at

    Sachin Dedhia

  • Develop a standard header for stored procedures, views and user-defined functions. Include details such as a brief description of the object, specification document, author, date created, change history, etc.

    Throughout the application, develop and maintain a uniform style of commenting. This improves the readability of the code and represents a professional approach.

    It's a shame that neither of these two interesting points get more than a mention.

  • The bottom line message in this article is "Comment your code if it's not absolutely clear what you are doing". 

    I like that.  It's what I do.  It's what I want my developers to do.


    Student of SQL and Golf, Master of Neither

  • It would be nice to have more elaborate examples.

    Sample code commenting style would make a great little web site.

  • At my current place of employment, over 6 years and 20 developers, there have only been 2 that comment with any regularity.

    The first one, who has long since moved on, simply put his name and a creation date in the sproc.  But at least I could then go back to him if I had a question.

    The other one is me. 



  • I agree, when I look at code the first thing I want to know is what it does, who created it and what it references.

    Shame the author didn't follow their own guidelines

    Avoid copying and pasting comments.

    -- Spearate cooments from comment characters with a white space

    /* Spearate cooments from comment characters with a white space */


    Spearate cooments from comment characters with a blank line


  • I think it is worth mentioning that both Apex SQLDoc and Innovasys DocumentX use special (and virtually identical) comment tags so that they can suck out the comments and build a compiled help file of your database.

    These two products are inexpensive.

    Because of what they do the benefit to the individual developer of commenting their code becomes very obvious. The larger the project the more important the comments become.

  • "Avoid framing block comments. It looks attractive initially but is difficult to maintain..."

    Not nearly as difficult as trying to maintain code without revision notes of who did what so you can go to them for more info about "why" they changed the code and why "like that".

    I have our citation as part of one of the templates we use for stored procs and scripts, so you ctrl+shift+m to populate the info and your form structure is set for you:


    FROM sysobjects

    WHERE name = N''

    AND type = 'P')








    = ,




    --STP: procedure_name







    SELECT @p1, @p2






    grant execute on TO PUBLIC


  • I've been at my current job for 5+ years and it seems like no one in our development dept ever provides comments in any type of code be it in the program code (we are a software company) or in DB objects such as SP's, UDF's, Triggers, ect.  We also have a proprietary scripting that allows our clients to create txt files that contain all teh information needed to create custom reports that can be excuted within our program.  We provide a large number of stock versions of these for clients to use.  We like the whole scripted report concept much better then reports that are hard coded into the program because it's much easier to update and maintain them.  The porblem is there are often no comments in those iether and so clients and those of us in support (where I work) have no idea how many are suppose to work.  I have voiced considerbale concern over this to no avail.  What finally did me in on this and made me give up was when I asked a senior VP about this one day.  I said that while doing so for existing DB objects may be to much to ask, can we at least put in a rule that from this point forward any items added to the schema can not be included in the final product that goes to the client until the developer has added comments to our DDF (Data Dictionary File similiar to an ERD).  His response was no and that asking them to do that was asking too much.  They had more important things to do then worry about documenting everything they do. 

    Can anyone share a personal experience that tops that?


    Kindest Regards,

    Just say No to Facebook!
  • Another way of doing it is to not comment at all.

    (Sorry, I could not be bothered to find the shocked face icon!)

    The reasoning is as follows: An inaccurate comment is more dangerous than no comment at all, as developers will read the comment and then not read the code to confirm it is true.

    Comments need to be kept up to date as much as any other documentation and sometimes they don't get kept up to date with the code.

    The fix for this is to write the code in such a way that the code is self-documenting, so if you need code that gets the mean sales figures for the previous three months you have a function/view/procedure called Previous3MonthsSales. If you have a block of code in a procedure that is hard to understand then take it out into it's own procedure and name the new procedure accordingly.

    The only discipline you then have is to ensure your objects only do what they say they do and nothing else, and they do it right! If someone wants Previous3MonthsSales changed to 4 months then create a new code object to do that and name it Previous4MonthsSales.

    Just an alternative view...

  • I saw you make a basic note at the top, then only comment complex, not obvious logic. And then add notes when you change something. Other than that, keep it simple.

  • What about the overhead of the comments themselves?  This if often overlooked.  It's my understanding that SQL does load comments (even full-line/multi-line comments) into the proc cache.  So lots of comments = lots of extra proc cache space that could be used for something else. 

    Thus, I prefer carefully chosen variable names with minimal comments on areas that are difficult or obscure.  Hopefully at some point SQL will get better internal code and simply drop comments from the code as it is loading the proc cache.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • I think the point was the asterisks on the right edge; over time they no longer line up as in his example. Your example does not use any characters on the right so you are following his recommendation.

  • It seems everyones has their own experiences and horror stories. One year a go I joined a company where all the previous developers had walked out in one day. The was no documentation (they never prepared it) and no user manuals. Talk about a struggle.

    Some form of documentation is needed to understand the business logic that is being implimented.  I tell my team that I'm not planning on leaving soon but I could be hit by a bus crossing the street tonight.

    All new development include header comments on who coded the program unit, when it was done, and why it was done. I go a step further and include comments on the arguments, the result set, any return values, as well as constants or configuration values that need to be set. I do this because it make it very easy to share documentation with consultants who are preparing external services for us.

    The most critical comments though, are the comments documenting changes.

    In the header we include the following:


    --  --------------------

    --  DEVELOPER:  R. B. Morrison

    --  DATE:       June 13, 2006

    --  REF:        20060613

    --  COMMENTS:   Modififed search criteria to remove the date added and

    --              include date modified.

    Within the code the following comments are made:

    SELECT  first_name

          , last_name

          , address

          , city

          , state

    --  START   20060613

    --      , date_added

          , date_modified

    --  END     20060613

    FROM    members

    Now when we need to find the changes that were made, we use the search tools of Query Analyzer (or what ever tool we are using) to find where the reference number occurs.

  • I have read the comments by some advocating less or little to no documentation and I do understand why you feel the way you do but that type of mentality is the wrong type in development.  There is no such thing as implied or understood code with anything but the simplest of processes.  If you have to choose between providing to little information and too much you should always err on the side of too much.  Why?  Because when you either document very little or none at all you provide the person that comes after you with no options at all but to guess at what your code does or means.  And even in the case of self-evident code it can be easier and a lot quicker to read a quick note from the code's author then to try and go thru their code and decipher what the intent is.  Take the case of complex SQL queries which any DB app uses.  If you opt to not explain what the query returns and what logic it uses to determine the result set as well as how the criteria work then not only are you placing a burden on those that follow you but you also do your customers and your co-wrkers a grave injustice.  As one who works in support and also does some development I can tell you that clients do not like it when we can't explain to them how for example a report works because the reports author didn't bother documenting it.   And no you can't just assume that the person(s) who review your code will understand it the way you did.  That works only in an ideal world where everyone thinks alike. 

    If you are too lazy to document your work then for the benefit of the rest of us please find something else to do.  As a support tech you are the worse kind of nightmare.  Not only are we unable to explain to our clients/users why something works the way it does or doesn't work the way they think it should but you also give the rest of develpment a bad name.  When enough of you do this kind of thing it makes everyone look like as if they are either too lazy to document their work or worse they don't document their work because they are not confident or perhaps even competent in their abilities.  After all if you don't document your code it's very difficult for some one to say it doesn't work because there's nothing from you in writing indicating how it's suppose to work. 

    Kindest Regards,

    Just say No to Facebook!

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

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