Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Comment SQL Server TSQL Code


By:   |   Read Comments   |   Related Tips: More > DBA Best Practices

Attend these FREE MSSQLTips webcasts >> click to register


Problem
I do a horrible job of commenting my T-SQL code in SQL Server.  I could use a few pointers because I have gone back to change my code and it has taken me forever.  How should I go about commenting my T-SQL code so it is simple and helpful?  Does any of the functionality differ between SQL Server 2000 and 2005?

-- A request from an MSSQLTips.com Subscriber

Solution
In response to your request, at a high level commenting code is the same between SQL Server 2000 and 2005.  In reality, commenting the code is more of a process than any sort of technology.  It is really up to you as a DBA\Developer to make it important to comment your SQL Server code, because whether your code is commented or not, it will still work if it is developed properly, but just might be more of an issue in the long term to enhance or maintain.

Benefits of Commenting Your T-SQL Code

  • Your team members can learn from your code by being able to read the comments and then review the techniques used in the code block.
  • Easier for you to change the code months or years after you have written with the code.
    • On an aside, I have worked with customers who have had my code running in production environments for over a year and have asked for a modification.  Based on how I comment my code, I can easily issue a 'Find' command (CTRL + F) for a keyword or two, take a look at the code and easily make the changes.  Had I not commented my code, it would have been time consuming process.
  • Means of checks and balances between what you need to functionally build versus what has been built based on comparing your comments to your requirements.

T-SQL Commenting Process

As mentioned before, commenting your code is nothing more than a process that needs to be followed as you build the code.  Below outlines some of the important types of information that should be included in your comments:

  • Summary comments
    • Object Name
    • Project
    • Business Process
    • Purpose
    • Detailed Description
    • Database
    • Dependent Objects
    • Called By or Application Screen
    • Upstream Systems\Databases
    • Downstream Systems\Databases
    • Revision History
      • Revision Number
      • Change Management Request or Ticket Number
      • Date
      • Developer
      • Change Summary
  • Inline comments
    • Description of the code block
    • Valuable information of interest that can be easily overlooked such as an obscure WHERE clause
    • As the code is updated reference the applicable revision number to correlate the comments
  • External comments
    • Associate a readme.txt file with the Visual Studio project or have a readme.txt file as portion of the code that is promoted between environments if Visual Studio projects are not being used
      • In this document, outline the following items:
        • Overall purpose of the code
        • Description for each file
        • Implementation notes
  • My process is as follows:
    • Build a template with the summary comment block listed in this tip and fill out as many of the items as possible when I start coding
    • Then begin coding and add a descriptive comment block within each section of the code
    • As I review the code, I make sure I have the accurate and descriptive comments inline to the code
    • Before I promote the code, update the summary and inline comments to make sure my team members will be able to functionally understand the code based on the comments

Example Comments

/*
----------------------------------------------------------------------------
-- Object Name:
-- Project:
-- Business Process:
-- Purpose:
-- Detailed Description: 
-- Database:
-- Dependent Objects:
-- Called By:
-- Upstream Systems:
-- Downstream Systems:
--
--------------------------------------------------------------------------------------
-- Rev | CMR | Date Modified | Developer | Change Summary
--------------------------------------------------------------------------------------
--       |          |                      |               |
--
*/

SET NOCOUNT ON

-- 1 - Variable declarations

-- 2 - Initialize variables

-- 3 - Next set of logic...

-- z - Object cleanup

SET NOCOUNT OFF
GO

Commenting Best Practices

  • Comment the overall code and explain what the code is doing in simple terms so that any DBA\Developer looking at the T-SQL code has an idea of what the code is trying to accomplish.
  • 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.
  • Build a process for commenting your code, no matter what your process may be, make sure you follow the process for each set of code you develop.
  • As you begin to enhance code that was developed before you put your commenting process in place, update the code with the summary and inline comments so the code is standardized, but also to ensure you understand what the code is doing.

Next Steps

  • Setup your own process for commenting your code.  One good approach is to comment the code as you write it and then as a portion of the final review and testing prior to promotion, validate that the comments are correct.
  • Before you promote code to production, make sure the code is well commented such that another person with minimal knowledge about the project could enhance and maintain the code.
  • Once you have built your commenting process, setup a policy in your company for commenting the code and get buy-in from your team members to have a consistent commenting practice across your applications.
  • As you enhance and maintain the previously developed code, create or update the comments to begin to have standardization across the enterprise with the previously developed applications.
  • Check out the following MSSQLTips.com tips:


Last Update:


signup button

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools