Rajendra Gupta
Modify comment configurations

An overview of SQL Comments

October 19, 2021 by

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.

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.

Sample SQL Script

In the below example, we commented on the column [BillingAmt] in the select statement. Therefore, you do not see it in the query output.

Single line SQL comment

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.

Comment using SSMS

If you select multiple lines and click on the comment out icon, it adds double dashes on each line, as shown below.

Comment out multiple lines

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.

Uncomment the selected lines

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.

Placing multiple line comments

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.

Start and end of a comment block

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.

Ignored text

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:

Output:

Nested block

You get the following error message if the nested comment does not have a closing block mark (*/).

Error message

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.

Nested block

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.

Adding comments

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.

Double dash

However, if you use slash star for comments, you do not get this problem, as shown below.

Use Slash Start for comments

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.

ApexSQL Refactor  comment options

Suppose we have a SQL script that includes the following double-dashes single-line comments.

Sample script with comments

In the ApexSQL Refractor properties, you can see a script preview on the right side with the existing configuration.

View ApexSQL option effect

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(*).

Modify comment configurations

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.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
Development, SQL commands, SQL Server Management Studio (SSMS), T-SQL

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is 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 his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

432 Views