T-SQL Comment Best Practices for SQL Server

By:   |   Updated: 2022-07-18   |   Comments (5)   |   Related: > TSQL


Problem

I need to understand more about commenting my Microsoft SQL Server Transact-SQL (T-SQL) code. What does it mean to comment a SQL query? How do I create comments? What is SQL syntax for comments?  Are there any best practices for commenting code in a SQL database?

Solution

Comments are lines of text that are added to code that are not read by the computer during execution. They are not lines of code, instead, they are meant for humans to read and understand. Programmers add comments to help the next developer who opens the code object understand what the code does. This is true even if the "next" developer is expected to be the same person as the original developer! It is very easy to forget what a code block does after some time away from it.

This SQL tutorial will show how to comment in T-SQL, comment syntax, and offer some advice on best practices regarding commenting.

Single Line Comments

There are 2 main methods of commenting T-SQL code.

The first is to put two dashes (or hyphens) at the beginning of the line. Any text on that line after the dashes will be ignored by the compiler. In both SQL Server Management Studio (SSMS) and Azure Data Studio (ADS) all comments will be shown in green.

SELECT * FROM Sales.Invoices;
 
--This is a comment and will be ignored by SQL Server.
 
--Even if a separate line looks like SQL commands it won't be executed.
 
--UPDATE Sales.Invoices
--SET DeliveryInstructions = 'Please place by the front door.'
--WHERE InvoiceID = 7; 
 
UPDATE Sales.Invoices
SET DeliveryInstructions = 'Throw it on the roof, please'
WHERE InvoiceID = 7;

Each of the prior examples shows an entire line of code is commented, but that doesn't have to be the case. If 2 dashes appear part way through a line of code then everything before the dashes will be treated as code and everything after will be treated as a comment as shown with this SQL statement.

UPDATE Sales.Invoices --This is a comment. The text preceding me is still code.
SET DeliveryInstructions = 'Throw it on the roof, please'
WHERE InvoiceID = 7;

Block Comments or Multi-Line Comment

The second way to create comments in T-SQL is to use a block comment.

A block comment is useful when many lines of code need to be commented at once. Rather than have to put the 2 dashes on many lines in a row, a single /* (i.e. slash and asterisk) combination of characters indicates the start of a block comment. When that combination appears in T-SQL, all text across any number of lines will be considered comments until the */ (i.e. asterisk and slash) combination of characters appears indicating the end of the block comment.

SELECT * FROM Sales.Invoices;
 
/*
This is a comment and will be ignored by SQL Server.
 
Even if it looks like code it won't be executed.
 
UPDATE Sales.Invoices
SET DeliveryInstructions = 'Please place by the front door.'
WHERE InvoiceID = 7; 
*/
 
UPDATE Sales.Invoices
SET DeliveryInstructions = 'Throw it on the roof, please'
WHERE InvoiceID = 7;

Although a much less popular use case, a block comment can also be useful for commenting on an internal part of a single line of code.

UPDATE Sales.Invoices
SET /*InternalComments = 'Customer is crazy',*/ DeliveryInstructions = 'Knock Loudly'
WHERE InvoiceID = 7;

Automatic SQL Comment Creation in SQL Server Code

Sometimes a block of code needs to be commented out quickly with the intention of uncommenting just as quickly. Luckily, SSMS and ADS both have a shortcut for this.

In SSMS these 2 buttons are displayed by default. The button on the left will add -- to the current line of code where the cursor resides. The one on the right will remove -- from the front of the line. If there is no -- on the line then the button on the right will not do anything.

These are the comment and uncomment buttons in SSMS.

When either button is clicked, the line where the cursor currently resides or all lines of code that are currently highlighted, even partially, will be affected.

To achieve the same effect in ADS use Ctrl + K, Ctrl + C keyboard combination to add -- comment and Ctrl + K, Ctrl + U to remove -- comment. These keyboard shortcuts will also work in SSMS.

These commands can be run repeatedly and will continue to add the comment dashes or remove them.

Check out this animated GIF to see these buttons in action including adding more than 2 dashes.

This animated screenshot shows the comment/uncomment buttons in action.

Why use SQL Comments?

Comments are a significant part of any T-SQL script or code object. They play an important role in helping any reader of the code understand what the code does, how it does it, and why it does it. They can help explain changes in the code over time. Sometimes the code seems so obvious to the developer at the time it is written that it seems silly to waste time adding comments. Invariably, it later makes little sense to anyone else. For that matter, it's amazing how much one forgets about code that they wrote after being away from it for a long time.

Without comments, any future reader of the code will surely be sorry and potentially lost as to why something was coded that way.

SQL Comment Examples

Here are some examples of good commenting practices.

Code Header

Any T-SQL code object should include a header that explains a few things about the object:

  • An explanation of the purpose of the object
  • A description of any parameters
  • A description of any output (parameters or query output)
  • A version history that includes
    • The author of the code or change
    • The date of the version
    • A brief reason for the version
    • Any tracking information (bug tracking system number, helpdesk request number, etc.)

The following example is what a good header might look like:

CREATE OR ALTER PROCEDURE dbo.CalculateInvoiceTotalByInvoiceDateRange
  @StartDate DATE
, @EndDate DATE
AS
/*********************************************************************************************
CalculateInvoiceTotalByInvoiceDateRange - This procedure will add up all the invoice lines for 
an entire invoice date range to get a total amount charged for the time period.  It was 
originally requested by the sales team and is usually run for a month or a week at a time.
 
PARAMETERS:
  @StartDate - The first shipping date (inclusive) to include in the calculation.
  @EndDate - The last shipping date (inclusive) to include in the calculation.
 
OUTPUT:
  The output is a single dataset meant to be used by an SSRS report of the same name as the procedure.
 
Version History:
  20210723 - Eric Blinn    - Initial release.  Request came in on helpdesk ticket 3352 from John Doe
  20220316 - Derek Blind   - John asked us to exclude customer 105 since they are billed separately.
                             Helpdesk ticket 4321.
 
*********************************************************************************************/

Explaining a section of code

When writing code, any time the code is anything other than a very plain statement or section, it makes sense to tell a future user what the code is doing and why. Even if it seems pretty obvious, it's much easier for the future developer to read the description than having to read through the code and deduce what is happening and why.

Consider this next example. Imagine how much longer it would take to work out what it was doing if there were no comments or only a few sporadic comments. For a fun exercise, copy and paste this code, remove all the comments, and hand it to someone that knows T-SQL. This isn't very long or complicated so surely, they could figure it out. But compare that to how long it took you when you had the comments to explain the steps along.

--This next block will take the log backup of each eligible database.
--We don't take a log backup of every database every time since they 
--aren't all busy at the same time.
 
DECLARE @DBName SYSNAME;
DECLARE @FileName NVARCHAR(400);
 
--First we create a cursor with the list of databases that need to have a log backup taken.
--We do that based on the DB having been 5MB of activity or it having been 4 or more hours
--since we last took one.
 
--Since the dm_db_log_stats DMO requires a parameter we needed to use an APPLY rather than a JOIN.
DECLARE DBList CURSOR LOCAL FAST_FORWARD FOR 
SELECT name
FROM sys.databases d
  OUTER APPLY sys.dm_db_log_stats(d.database_id) ls
WHERE log_since_last_log_backup_mb > 5
  OR ls.log_backup_time > DATEADD(hh, -4, SYSDATETIME());
 
OPEN DBList;
FETCH NEXT FROM DBList INTO @DBName;
 
WHILE @@FETCH_STATUS = 0
BEGIN
  --We use the DBName plus a timestamp based on the current system clock for file names.
  SET @FileName = N'D:\SQLBackups\' + @DBName + CONVERT(NVARCHAR(10), SYSDATETIME(), 112) + N'_' + REPLACE(CONVERT(NVARCHAR(10), SYSDATETIME(), 108), ':', '') + N'.trn';
  
  --Time to take the backup
  BACKUP DATABASE @DBName TO DISK = @FileName WITH COMPRESSION;
  
  --Move on to the next database in the list
  FETCH NEXT FROM DBList INTO @DBName;
 
END; --this ends the cursor loop
 
--Cleanup of objects no longer needed.
CLOSE DBList;
DEALLOCATE DBList;

Showing history of code

Hopefully, any T-SQL code object has a history log at the top, but more detailed information about the changes should be recorded within the object itself nearer to the actual code changes. Here is an example of a change being commented on at the point of the change.

SELECT *
FROM Sales.Invoices INV
  INNER JOIN Sales.InvoiceLines IL ON INV.InvoiceID = IL.InvoiceID -- SQL JOINs
WHERE INV.InvoiceDate BETWEEN @StartDate AND @EndDate
   AND INV.CustomerID <> 105; --20220316 - Added this exclusion at user request

Final Thoughts

Make sure any code that you write is well documented with lots of verbose and useful comments.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Eric Blinn Eric Blinn is the Sr. Data Architect for Squire Patton Boggs. He is also a SQL author and PASS Local Group leader.

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

View all my tips


Article Last Updated: 2022-07-18

Comments For This Article




Tuesday, July 19, 2022 - 7:57:48 AM - JR Shortall Back To Top (90281)
I moved to block-style comments for all comments a few years ago for code that goes into production.
The reason is because when the code is compiled and executed it becomes one line in the sql_handle. When you pull that out, you can't tell where the comment ends and makes it more difficult to triage a production issue.

Just my 2c based on experience in production.

Monday, July 18, 2022 - 5:07:16 AM - Bob Back To Top (90276)
Comments can be valuable - thanks for your article. Comments are like code in that they need to justify their existence. Just as code can have bugs, so can comments.

So I'd suggest that you don't put these in comments:
* Version history - any code artefact of value should be in a version control system such as Git, which can tie versions to commit messages, who did the work, accompanying changes to tests, user stories etc.
* A description of the code beyond a *one normal-length sentence* summary at the top of the file.

These give more words to read / skip over without adding enough value.

A good rule of thumb for me with comments is that they contain words such as 'because' or 'so'. They are a clue that the comment explains *why* the code is as it is (or why it isn't in some other way that you might expect). This is something that even good structure and good names can't always help with.

Monday, July 18, 2022 - 5:02:57 AM - Mark Belshaw Back To Top (90275)
Nice summary of what should be commented and why, especially the references to helpdesk tickets, which can be incredibly useful.

One point I'd like to add relates to the use of code search tools. I use AgentRansack to run though the DevOps local folder of .SQL scripts to locate e.g. references to a particular table / synonym. AgentRansack only lists the individual lines of script containing those references, but is not aware of T-SQL comment structures. The point being, a line of T-SQL commented out using two or more dashes doesn't need to be investigated - you can see its a comment right there in the search. A line in the middle of a block commented out using /*....*/ has to be followed up in SSMS, taking more time.

The upshot - I always comment out blocks of code using to dashes and leave the /*...*/ construct for commenting out part of a line. But, then, maybe everyone else has better search tools than I do!

Thursday, June 30, 2022 - 9:49:56 AM - Greg Robidoux Back To Top (90225)
Hi Kenneth,

thanks for pointing that out. This has been updated.

-Greg

Wednesday, June 29, 2022 - 10:55:31 PM - Kenneth Gladden Back To Top (90224)
In Block Comments the end of the block is an asterisk followed by a slash, not a backslash.














get free sql tips
agree to terms