In a typical database, we have multi lines or page SQL statements in a view, SQL statement or the stored procedure. Also, multiple developers change the T-SQL, and it becomes difficult to track who altered the code and for what reason.
The SQL comments in a statement help you to read and maintain the SQL scripts easily. For example, you can put a comment that showcases the purpose of the statement, such as adding a specific query hint or modifying script logic. SQL Server does not execute these comments, and it does not affect query behavior as well.
Another purpose of the SQL comment is to disable the execution of a specific code. For example, suppose you do not want to execute the where block condition in a select statement. Therefore, put a comment on where the block and SQL Server skips execution.
You can add comments between the keywords, parameters at the beginning or end of a statement. SQL Comments can be added in the following formats.
Single-Line Comments
The single line SQL comment uses two dashes (–) in SQL Server. Once you add the two dashes, SQL Server ignores the text written after these dashes in a single line. It is known as commenting out. You can also see a different color in SQL Server Management Studio (SSMS) after you commented on a piece of code.
For example, here, we add a comment in the script to highlight the purpose of the query.
|
1 2 3 4 |
--Script for Select Top ‘N’ Rows command from SSMS SELECT TOP (1000) [ID] ,[Auditdata] FROM [azuredemodatabase].[dbo].[AuditLogs] |
Another example shows I comment out a single line of code using the double dashes. The SSMS IDE changed the text color to green, and SQL Server skips executing this piece of code.
In the below example, we commented on the column [BillingAmt] in the select statement. Therefore, you do not see it in the query output.
The SQL Server Management Studio has an icon to add the double dashes for commenting out a single line character.
Select the line and click on the highlighted icon in SSMS. It puts double dashes in front of the line.
If you select multiple lines and click on the comment out icon, it adds double dashes on each line, as shown below.
You can also use shortcut keys CTRL +K CTRL+C for putting out comments quickly. Similarly, you can click on the highlighted icon to remove the single-line comment. Its shortcut key is CTRL+K CTRL+U.
Block or Multi-line SQL Comment
If we comment using the double dash /(–), SQL Server considers it for a single line. If we need to comment out multiple lines, you need to put a double dash on each line.
It is not a feasible solution if we want to comment out multiple lines of code. Therefore, SQL Server uses multi-line comments that start with /* and ends with */. SQL Server ignores the texts, code written inside these.
You can also use these multi-line comments for displaying useful information about the script. For example, the below script adds the object information, script date, author, description on the stored procedure script.
If any developer scripts out stored procedure later, he can see brief information on who and what was modified.
Nested SQL Comment
We can have nested comments in SQL Server. For example, if you specify the block open (/*) in existing comments, SQL Server treats it as a nested comment. Therefore, the nested comment should have a corresponding block closed(/*) comment mark. In case you do not specify a nested comment block close mark, you get an error message.
Nested comment example:
|
1 2 3 4 5 6 7 8 9 10 |
DECLARE @Text AS VARCHAR(20); GO /* --Outer Block opened SELECT @comment = '/*'; --Nested Block opened */ --Nested Block closed */ --Outer Block Closed SELECT @@VERSION; GO |
Output:
You get the following error message if the nested comment does not have a closing block mark (*/).
SQL Server uses the Go statement to separate multiple batches in the scripts. However, you cannot use the go statements in the nested multiple line comments because SQL Server reads the go character in a line and considers it a separation of the batch. Therefore, if you try to use it, you get the following syntax errors.
Avoid using SQL Comment using double dashes
As shown earlier, we can add comments using double dashes (–) and the Slash start(/* ..*/) in the SQL Script. However, you should avoid the comments using double dashes(–).
For example, let’s say we add a comment in the following SQL script.
Sometimes, few monitoring tools or diagnostic management views put the entire script in one long line. If we convert the SQL script in a single line, it becomes invalid as SQL Server ignores all texts, code written after the double dash.
However, if you use slash star for comments, you do not get this problem, as shown below.
ApexSQL Refactor (SQL format) for SQL Comments
ApexSQL Refractor tool is helpful for formatting SQL scripts depending on the user requirements. For example, if we do not want single-line comments (double dashes) and convert them into block comments, ApexSQL Refractor helps you.
You can download ApexSQL Refractor as an SSMS plugin. Now, open SSMS and navigate to ApexSQL > ApexSQL Refractor > Options. In the comments section, you can see the following default configuration.
Suppose we have a SQL script that includes the following double-dashes single-line comments.
In the ApexSQL Refractor properties, you can see a script preview on the right side with the existing configuration.
Create a new ApexSQL profile and put a tick on the following configurations.
- Add border above block comments using *
- Add border below block comments using *
- Change all comments to block comments
- Enforce
You can see that it converts all comments to block comments and places a border above and below using Asterix(*).
Conclusion
This article explored the SQL Comments and their types to provide helpful information in the scripts. These comments are beneficial to avoid the execution of a particular portion of the SQL script instead of deleting it. You can use the ApexSQL Refractor tool for converting all comments in the block comments with a simplified process.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023