T-SQL Comment Best Practices for SQL Server
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?
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.
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.
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.
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
Make sure any code that you write is well documented with lots of verbose and useful comments.
- SQL Server Queries Explained
- Stored Procedure Tutorial
- Syntax Rules for T-SQL Development
- Using comments in a SQL Server stored procedure
- Comment SQL Server T-SQL Code
About the author
View all my tips
Article Last Updated: 2022-07-18