Writing better Transact-SQL code with good comments

So, I write IT software, and much of the time, that means I'm dealing with Microsoft SQL Server. Funny thing about that... I've worked with a lot of programmers over the years, good programmers... but it never ceases to amaze me how all of their good coding habits go out the window when they need to write some Transact-SQL code.

Here are some tips to make life easier on yourself and those who will need to read and maintain the code you've written, through effective and creative use of comments.

Positive patterns:

1. Use descriptive comments

Comments in code should always describe what the code needs to accomplish, rather than how the code functions. For example:

    /* insert reward discount for referring a customer */

Rather than:

    /* execute insert on CustomerRewards table */

2. Use comments to clarify the structure of your code

Structural comments can be used sparingly as part of a coding standard to introduce useful structure where the language elements do not provide enough. Some useful "structure" comments include using comments to "name" code blocks enclosed by BEGIN and END statements, such as:

    BEGIN /* customer record is new */
    ...
    END /* customer record is new */

Another kind of structure comment can add a "THEN" to the T-SQL IF statement:

    IF EXISTS
        (
        SELECT
            *
        FROM
            CustomerDiscounts
        WHERE
            CustomerID = @CustomerID
        )
    /* THEN */
        BEGIN /* customer has a discount */
        ...
        END /* customer has a discount */
    ELSE
        BEGIN /* customer does not have a discount */
        ...
        END /* customer does not have a discount */

Implementing the above structure for if statements particularly enhances readability where there is a long test expression IF clause, by clearly seperating the expression from the nominal case, just as the ELSE seperates the exception case from the nominal case.

3. Distinguish explantory comments from planning comments

I recommend using /*...*/ style comments for anything meant to live on as comments in production code.

For to-do notes, or pseudocode, both of which should be removed before the system moves into production, I recommend using "--" comments. This makes them easy to find.

4. History comments

I have never personally found these helpful, although capturing data about how often particular code is touched, and for what reasons, can be useful. If they are used, they are best put in a footer to keep the routine declaration close to the code.

5. Summary comments

A very brief header summary, which describes the requirements of the routine (but not the implementation) is useful. However, structured comments which include Author, Create Date, etc., offer very little value, IMHO.

Comment Anti-Patterns:

1. Reduntant comments

Comments that describe how, rather than what, are worse than useless.

2. Dead code

Commenting out code which is no longer executed because of requirements or implementation changes. This should be removed from the code. If you need to see it later, you can get it out of your revision control system.

3. "Paper-trail" comments

Eg. -- column added 2/2/1993 by JC

Why?

4. CYA comments

Eg. -- This code will break when two customers try to buy the same product

Comments like this give carte blanche to do almost anything, no matter how low quality. This leads to better solutions not being sought out.

If this is the only place risks and quality problems are captured, there is no opportunity to see them all together in order to prioritize fixes, or to see emerging patterns that require a pattern-based solution

5. Planning comments

For positive patterns in planning comments, see number 3 above. Negative planning comments include things such as "need to implement xxx" or "should update to include xyz". If this is the only mechanism for capturing technical needs, there is no visibility during planning activities, and there is no opportunity to see them all together in order to prioritize.

Further reading:

Steve McConnell's treatment of use of comments in Code Complete is excellent.