Comment SQL Server TSQL Code

By: Jeremy Kadlec   |   Updated: 2007-04-04   |   Comments   |   Related: More > DBA Best Practices

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

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:
  • Query Analyzer Shortcuts
  • SQL Server 2005 Exposed = Data Modeling Tools
  • The MSSQLTips.com T-SQL tips category
  • The MSSQLTips.com Scripts tips category

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips