SQL SERVER – SQL Basics: Code Comments – Day 4 of 10

This is the 4th post out of my 10 post series of my 10th book – SQL Basics. Today will show the importance of data and information.

You can get that in Paperback (USA) and Kindle (Worldwide).

Code Comments

Old classic movies utter this famous phrase “Gentlemen, this is off the record”. In the 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 commenting. When writing a query in the SQL Server query window, we can use special symbols to instruct the query engine to ignore these portions of the code when the query is run.

These special symbols also allow comments to visually look different than the rest of our code, which makes it much easier for the human eye to distinguish from the rest of the code as being something unique, and indicates this portion has a special purpose meant specifically for a human to read and for the computer to ignore.

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 ‘readme’ file allowing other people reading it to learn more about the code being used.

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. This is a more specialized use of comments that every good SQL Developer needs to know.

Single-Line Comments

How can we make just one line in a query window not run, while the other lines of code still run as expected? Typing two hyphen signs ‘–‘, one after the other without a space, will instruct SQL Server to ignore every character that follows it on the same line. This coding technique is known as a single-line comment.

In the figure 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.

--This query joins the Employee and Location tables together.
SELECT *
FROM Employee AS em
INNER JOIN Location AS lo
ON em.LocationID = lo.LocationID

However; the description is overly simple, as it does not explain why the two tables are being joined together, and what service or department might need the results from this query.

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.

A better example of how comments can be used to describe code in a work environment is shown in the figure below. While adding in more detail by indicating who the code is written for (HR), the specific purpose of why the code exists (Employee Report), and also draws attention to which fields from each table need to be included in the results (all Employee fields and the Address, City, and State Location fields).

Since the comments in this example take up two lines in the query window, it is necessary to start each new line of comments with a new set of double hyphen signs, so SQL Server will know to ignore the first two lines of the query.

--This gets all fields from Employee table
--plus Address, City, & State from the Location table
SELECT em.*, lo.Street, lo.City, lo.[State] FROM Employee AS em
INNER JOIN Location AS lo
ON em.LocationID = lo.LocationID

It is also possible to use single-line comments for testing specific segments of code. This commenting technique gives us the ability to temporarily prevent a segment of the code block from being able to execute, while the remaining code we want to inspect closer is able to run.

Let’s say that while building the code for the HR Employee Report, something was not working right. 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. 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.

This can be accomplished by commenting out the last two lines of the code, which will instruct the SQL Server query engine to ignore these two lines of code and only run the first two lines of code. The following code will not execute the INNER JOIN for the Location table and will only return the records from the Employee table, as shown in the figure below.

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

Multi-Line Comments

There are times when we want to run part of a query or just a few lines for a quick test. Once the test is over we want all of the code back in its original state. For these purposes many SQL developers choose to highlight (select) only the segment of code they are interested in executing and ignore the remaining code block.

This next code example demonstrates how it is possible to run a segment of code that is part of a larger code block without using any commenting techniques.

Notice that the SELECT and FROM lines of the query are highlighted in blue for the Employee table. While the INNER JOIN is part of our query, since it is not highlighted (selected), this segment of code will not run when the segment of code that is highlighted in blue gets executed. When we run the highlighted segment of the query, we see that only the fields from the Employee table are returned. The segment of code with the INNER JOIN is ignored by the query engine.

--This query joins the Employee & Location tables together
SELECT *
FROM Employee AS em
INNER JOIN Location AS lo
ON em.LocationID = lo.LocationID

When we want to comment out many consecutive lines of code, we have two different commenting techniques we can use to accomplish this goal. Use single-line comments for each and every line to be prevented from running, or use the more efficient multi-line commenting technique to disable small or large segments of code with an easy to use beginning and ending sign.

The first option is to use single-line comments by placing the double hyphen sign at the beginning of each line we want to prevent running when the query is executed. We used this technique in the previous section, so the following code should look familiar.

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

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). Let’s see how this works by using the same query we have been practicing with. The results of using this multi-line commenting technique are the same as those returned by using single-line comments.

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

In this example of commenting out only two lines of code, typing two sets of ‘–‘ signs, is about the same amount of work as typing a matching set of ‘/*’ and ‘*/’ signs. If this is the case, why choose one technique over the other?

The amount of effort to type the ‘–‘ signs for a single-line comment increases dramatically as the number of continuous lines to be commented out grows. What if we wanted to disable the last 300 lines of code? Typing ‘/*’ and ‘*/’ signs one time each, is definitely easier than typing the ‘–‘ sign 300 times to achieve the exact same result. Unlike the double hyphen, which can only instruct SQL Server to ignore one line of code at a time, the ‘/*’ ‘*/’ signs (delimiters) are more efficient for multi-line commenting as there can be an infinite number of lines of code between the opening and closing delimiters.

Imagine if we had needed to comment out these 300 lines of code for testing purposes and as soon as we completed our test, it was necessary to uncomment the same 300 lines of code for the next member of our team to review. Removing one set of opening and closing ‘/*’ ‘*/’ delimiters is much simpler and faster than removing 300 sets of double hyphens!

Action Item

Get the book for yourself and your friend. This is just a reference everyone must have it.

Available in Paperback (USA), Kindle (Worldwide) 

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