Comment SQL Server TSQL Code

By: Jeremy Kadlec | Read Comments (1) | Print Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009. Related Tips: More

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

Related Tips: More | Become a paid author

Last Update: 4/4/2007

Share:

Share

Tweet

Free SQL Server Learning The SQL Server Security THREAT - It’s Closer Than You Think Tutorial - SQL Server Performance Tuning and Monitoring

Comments and Feedback:

Friday, March 16, 2012 - 4:17:50 AM - Theresia

Read The Tip

hey, total n00b here. i really like this idea, ceabsue I don't want a login for my current project, and every other tutorial requires a login set-up first. lol I am totally new to php and I would really appreciate it if someone could tell me how to create an empty file' for the comments. Does that mean I write nothing at all in it, or do I still set it up with html headings and have a place where the messages fall into? i dunno wat I'm doing wrong

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Try the free performance monitoring tool from Idera! It takes just 5 minutes to connect your SQL Databases to source control. Got 5 minutes? Get started now. SQL Servers keeping you up at night? Contact the Edgewood SQL Server consultants for a Health Check. Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise. Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. Valuable SQL Server web casts on Performance Tuning, Development, Administration, SSIS and more...