As I am easing back into real life from writing the book, I am in search of easy targets for blogging.  My boss mentioned this blog over on Jeff Atwood’s Coding Horror Blog and it got me thinking about commenting.  His advice is to only comment “why” the code works.  I can’t quite agree, because the code he claims to be acceptable is:

private double SquareRootApproximation(n) {
  r = n / 2;
  while ( abs( r – (n/r) ) > t ) {
    r = 0.5 * ( r + (n/r) );
  }
  return r;
}
System.out.println( “r = ” + SquareRootApproximation(r) );

I mean, it is better than some code I have seen,  but still, I would like a bit more information about why this works.  Maybe the name of the algorithm used, or at least what to do if this fails to provide the expected results.  Admittedly this is probably something that could be easily found, but most algorithms are not.  Comments in my mind should at least lead you to understand the mindset of the programmer.  What would actually improve this code in my mind is to change the variables to full words (though in this case it might not make sense to do this.)

On an extremely different side of things is this article from “Edgewood Solutions Engineers” on mssqltips.com. Their answer is to explain what the code is doing in simple terms, making sure to comment almost everything.  They have a very elaborate header devised, with dependencies, both users of the object and objects it used.  Most of what is said seems a bit like overkill, but their point here “Comment all of the major code blocks of the code and the critical minor points that can be easily overlooked such as a obscure WHERE clause.” is a good one.  I generally pepper my code with comments where I think it will be hard to debug for myself later, with a consideration for others, particularly when those others will call me to explain the code.

Which brings me to my commenting philosophy. I personally think you have to comment to the expected lowest common denominator.  Think of the dumbest person who could have the need to read your code who is also qualified to have their job (otherwise you would have to write instructions on every line of code). If the qualified person can figure out what you are doing just by your naming conventions and , then it doesn’t need comments. But if that person would look at the code and reasonable figure it out, then there is no need to comment the code.  What this requires is a few things:

• Naming objects – if your procedures, tables, columns, functions all have meaningful names, you won’t have to explain what they mean, saving time
• Good design – if the relationship between objects and the cardinality of those relationships is clear, then you don’t need to explain that what you are doing is hack due to poor thinking…
• Naming variables – probably the most important thing to avoid the need for comments is naming stuff.  Name variables with words, not single character values (except sometimes i, x, etc will suffice for obvious typical uses)
• Reasonable code formatting – SQL has no real form, so you could write procedures on a single line.  You could.  You could smash your hand with a hammer too.  Neither action would be very good.  (Consider using Red-Gate’s SQL Refactor tool if nothing else.)

However, the fact is, for SQL code, the real problem comes in when you start coming up with cool relational methods of solving problems that most moderately qualified people wouldn’t get. For example the trick of using a sequence table to break apart a comma delimited list. Couple that with a join and you get some amazingly cool code, but how do you comment it?

For example, say an architect that shouldn’t be an architect designs a table with a comma delimited list like this (didn’t I mention good design earlier?  I hate having to say this is a hack, but it is an elegant hack…)

–excerpted from Chapter 7 of Pro SQL Server 2008 Relational Database Design and Implementation
CREATE TABLE poorDesign
(
     poorDesignId int,
     badValue varchar(20)
)

INSERT INTO poorDesign –using 2008 syntax
VALUES (1,’1,3,56,7,3,6′),
            (2,’22,3′),
            (3,’1′)

You can “normalize” this set using a table of numbers (in my examples named tools.sequence) and a really cool join:

SELECT    poorDesign.poorDesignId as betterDesignId,
          SUBSTRING(‘,’ + poorDesign.badValue + ‘,’,i + 1,
                CHARINDEX(‘,’,’,’ + poorDesign.badValue + ‘,’,i + 1) – i – 1)
                               as betterScalarValue
FROM     poorDesign
             JOIN tools.sequence
                    on i >= 1
                      AND i < LEN(‘,’ + poorDesign.badValue + ‘,’) – 1
                      AND SUBSTRING(‘,’ + + poorDesign.badValue + ‘,’, i, 1) = ‘,’

But are there enough pixels available on the planet to make that more understandable to most SQL programmers? Even the reasonably qualified?   I mean, I am still kind of amazed at the technique and the fact that it returns the following:

betterDesignId betterScalarValue
————– —————–
             1                 1
             1                 3
             1                56
             1                 7
             1                 3
             1                 6
             2                22
             2                 3
             3                 1

still impresses me.  Frankly I don’t know how to comment that code to make it readable.  In a real situation I would settle for a comment before the SELECT that stated:

–Uses a table of numbers to parse the comma delimited list into a SQL acceptable format.
–If you don’t understand this code, read this article: http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum

Opinions? What do you use for a comments in your code?  Do you have commenting policies?