SQL SERVER – Beginning SQL 2012 – Why we use Code Comments – Abstract from Joes 2 Pros Volume 1

Old classic movies utter this famous phrase “Gentlemen, this is off the record”.  In movies this is used when talking to the press and letting them know a certain comment or two will be said, however it is not meant for publication in the media. Sometimes, we want to use words or phrases within a query window that we want SQL Server to ignore when executing the code. Fortunately, SQL Server allows us to write words or phrases that are “off the record”, with a coding technique called a commenting. Let us understand why we code comments.

All supporting files are available with a free download from the www.Joes2Pros.com web site. This example is from the “Beginning SQL 2012 Joes 2 Pros tutorial series Volume “.

In the file SQLQueries2012Vol1Chapter11.0Setup.sql, if you need help setting up then look in the “Free Videos” section on Joes2Pros under “Getting Started” called “How to install your labs”

Why we use Code Comments

Thus the main purpose of commenting techniques is to allow us to write words or phrases that are often descriptive notes or warnings of what the code is doing, when to run it and why it is necessary. This is a big benefit when other people, such as our team members, supervisors, or software testers are reviewing the code for their own purposes. In other words, comments are commonly used as a mini informational ‘readmefile allowing other people reading it to learn more about the code being used.

Single-Line Comments for Communicating

In the example below we see a query with a single-line comment written as the first line, describing the purpose of the code below it. The comment in this example shows other developers and testers what action the code is expected to accomplish. Indeed, when we run this code block, the result set contains all the fields from the Employee table and the Location table based on the matching LocationID field using the INNER JOIN keyword.

SQL SERVER - Beginning SQL 2012 - Why we use Code Comments - Abstract from Joes 2 Pros Volume 1 j2p2012-1

Use Comments to Say “Why”

It is important to use comments wisely, by considering who, what, when, where, and why for describing the purpose of the code. The challenge is to write notes that balance being descriptive and brief, so anyone else that may read it days, weeks, months or even years from now can understand why it exists and what it is meant to achieve.

SQL SERVER - Beginning SQL 2012 - Why we use Code Comments - Abstract from Joes 2 Pros Volume 1 j2p2012-2

Single-Line Comments for Trouble-Shooting

Another great use of commenting techniques is to troubleshoot or test smaller segments of code without needing to modify or delete surrounding blocks of code that are around the segment we want to run by itself.

We suspect there is a problem with the join to the Location table and believe that the code for the Employee table segment is correct. The ability to temporarily prevent a segment of the code block from being able to execute, while the remaining code runs is a valuable troubleshooting tool. We want to test our theory by only running the segment of code with the SELECT statement for the Employee table, without having to rewrite it as a separate query, or deleting any of the code in the INNER JOIN segment.

FROM Employee AS em
--INNER JOIN Location as lo
--ON em.LocationID = lo.LocationID
44KennsonDavid1996-03-16…111Has Tenure








12 rows

Multi-Line Comments

When we want to comment out many consecutive lines of code, we have two different commenting techniques to accomplish this goal. Use single-line comments for each and every line to be prevented from running like the last example. The second option is to use multi-line comments by placing a ‘/*’ (forward slash, asterisk with no spaces) sign at the start of the segment to be commented out and then close the comment with a ‘*/’ (asterisk, forward slash with no spaces).

FROM Employee AS em
/*INNER JOIN Location as lo
ON em.LocationID = lo.LocationID*/

In-Line Comments

What if we wanted to disable a specific part of code located on the same line as other code that we wanted to remain unaffected? The goal might be to disable one word in a long line of words within the code. We can achieve this goal by using an in-line commenting technique. In-line comments are best accomplished by using the same delimiters we used for making multi-line comments. We simply mark the beginning and ending of the word, or words, that we need to comment out of the code when the query is run. In this example, we want to display a report without the GrantID field. If we try to accomplish this by placing the ‘–‘ (double hyphens) delimiter, in front of the GrantID field, it’s going to prevent all the fields in the SELECT statement from being seen by SQL Server.

SELECT --GrantID, GrantName, EmpID, Amount
FROM [Grant]
-- OR Amount < 10000
OR EmpID = 7
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘FROM’

0 rows

The best way to accomplish the task of preventing the GrantID field from being displayed in our result set is to use the ‘/*’ ‘*/’ delimiters. Using this technique, we can easily prevent the smallest elements of a code block from executing, without moving or affecting the code surrounding it. The following example demonstrates how this is achieved.

SELECT /*GrantID,*/ GrantName, EmpID, Amount
FROM [Grant]
-- OR Amount < 10000
OR EmpID = 7
192 Purr_Scents %% team74750.00
3Norman’s OutreachNULL21000.00




4 rows

Book On Amazon 

Reference: Pinal Dave (https://blog.sqlauthority.com)

Joes 2 Pros, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Cumulative Update Released in February 2013 for SQL Server Editions
Next Post
SQL SERVER – Beginning SQL 2012 – Aggregation Functions – Abstract from Joes 2 Pros Volume 2

Related Posts

4 Comments. Leave new

  • Pinal, I wanted to ask your expertise on the best way to modify all the columns in my database from numeric(12,4) to numeric (18,4) while keeping all data correct with no losses. I am using sql 2008 and have a huge database and not sure the best way to make this change to allow for more characters throughout my entire database. There are many many tables that have this datatype that needs to be changed. Any advice you can provide will be greatly appreciated.

    • sandykumaryadav
      February 26, 2013 4:28 pm

      Hi Pinal , if we use 2 / at the end then how we will handle this.
      SELECT /*GrantID,/*/ GrantName, EmpID, Amount
      FROM [Grant]
      — OR Amount < 10000
      OR EmpID = 7

  • Can you have too many comments in your code? Do comments impact on performance?


Leave a Reply