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

This chapter is abstract from the Beginning SQL 2012 – Joes 2 Pros Volume 1

You can get the Five part SQL Server 2012 Joes 2 Pros Combo Kit for complete reference. 

Book On Amazon | Book On Flipkart

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.

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.

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.

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.

SELECT *
 FROM Employee AS em
 --INNER JOIN Location as lo
 --ON em.LocationID = lo.LocationID
  EmpID LastName FirstName HireDate LocationID ManagerID Status
1 1 Adams Alex 2001-01-01… 1 11 NULL
2 2 Brown Barry 2002-08-12… 1 11 NULL
3 3 Osako Lee 1999-09-01… 2 11 NULL
4 4 Kennson David 1996-03-16… 1 11 Has Tenure
5 5 Bender Eric 2007-05-17… 1 11 NULL
6 6 Kendall Lisa 2001-11-15… 4 4 NULL

 

 

 

 

 

 

 

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

SELECT *
 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]
 WHERE EmpID IS NULL
 -- OR Amount < 10000
 OR EmpID = 7
Messages
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]
 WHERE EmpID IS NULL
 -- OR Amount < 10000
 OR EmpID = 7
  GrantName EmpID Amount
1 92 Purr_Scents %% team 7 4750.00
2 Robert@BigStarBank.com 7 18100.00
3 Norman’s Outreach NULL 21000.00
4 @Last-U-Can-Help 7 25000.00

 

 

 

4 rows

Book On Amazon | Book On Flipkart

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

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

  1. 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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s