Practices that are ideal and even superior, in regards to coding, are practices that improve and add value to code. Adding comments to your SQL code is considered best practice for many reasons. Ideal code includes characteristics such as a good level of readability. Readability of code involves the level of ability to read and clearly understand code that has been written. As with the concept of an author including descriptions of event, places, and characters for a story in order for readers to understand the story; an author of code should also include descriptions of what lines of code should do and other explanations for code in order to assist readers.
Options for adding comments to your SQL code include the following:
Reasons why comments are useful
As you gain practice with using SQL, you may find that there are cases in which you return to code that you had written and question why you had used some of the code. The concept of adding comments to code can be applicable to many programming languages. Personally, I recall when I had started writing my first programs. When I wrote a program, of course I had known why I had used specific code and initially imagined that I would be able to remember what I had intended to accomplish with each line in the programs. However, after becoming engaged in many projects and then returning to projects I had completed months before, I found that recalling the intention of each line required research before I could move forward with more recent projects. Thus, a major advantage to adding comments is conservation of time.
An additional major advantage to SQL comments would be the supporting information that comments may provide greatly help with verification. There may also be cases in which others would find it helpful to be able to understand why you had used specific code. For instance, if a database administrator is updating some of the code used for a database and they look at sections that had been written by someone else, then it may be much easier for the administrator to clearly see why some sections had included some of the names, tables, statements, clauses, etc. that had been used. Perhaps you want to take a vacation at some point and someone else will be helping with modifications of a database or you will be sending your work to someone else who will then need to know the basis of your reasoning.
Examples of using SQL comments
A comment line is notated with two dashes. For example, the following is an example of how a line comment should look:
– – This is a comment in SQL
In this example, you will see a line comment incorporated with other SQL code.
create view student_members_view as select student_id, student_dept, student_first, student_last from Students_tbl where student_dept = ‘SCI’ - -selecting students in Science department order by student_id;
As you can see; the comment, “selecting students in Science department”, had been added. The writer of the code wanted to indicate that the view that had been created had used a column called “student_dept” and that the value of “SCI” had been used for “Science”. In this case, it may have been quite feasible for someone reading the code to develop the assumption that “SCI” had been for ‘Science”; however, it is always better to have supporting information. If the terms used had been more mysterious, then more research would have been required to develop an understanding of the reasoning used by the author. Beginning to explore SQL queries? Learn more with a Udemy class.
In this example, you will be able to see a Multiline comment used. As indicated in the name, Multiline comments include more than one line of comments. Notation in the form of the combination of forward slash with asterisk and then asterisk with forward slash is respectively used to signal for the beginning and then ending of a block of comments. The following is a basic example of a Multiline comment:
/* This is the beginning of the block of comments which is known as a multiline comment in SQL. This is the middle of the block of comments. This is the end of the block of comments*/
In the following example, you will see a Multiline comment used along with the code used earlier:
/*I have created the following view for the main student table so that I can have a virtual representation of the students table that includes values from columns for student ids, student departments, student first names, and student last names. I have included information for the students who are members of the Science department. I have decided to order the results by the student ids.*/ create view student_members_view as select student_id, student_dept, student_first, student_last from Students_tbl where student_dept = ‘SCI’ order by student_id;
In the following example, you will see a Multiline comment used long with the code for creating a table:
/*I am creating a table for students which will include the student id numbers, as indicated with a column name of student_id and a datatype of int; the student departments, as indicated with a column name of student_dept and a datatype of varchar; the student first names, as indicated with a column name of student_first and a datatype of varchar; student last names as indicated with a column name of student_last and a datatype of varchar. The departments use a three letter identification; SCI is for the Science department, HTH is for the Health and Nursing department, HST is for the History department, and ENG is for the English department. This table is typically updated with a new student enrolls or a student transitions to a different department.*/
create table Students_tbl ( student_id int(5), student_dept varchar(5), student_first varchar (20), student_last varchar (20),); insert into Students_tbl values (10110, 'SCI', 'Kathy', 'Richardson'); insert into Students_tbl values (10450, 'SCI', 'Jennifer', 'Alston'); insert into Students_tbl values (10720, 'HST', 'Kelvin', 'Mosely'); insert into Students_tbl values (10870, 'SCI', 'Meagan', 'Reddin'); insert into Students_tbl values (10430, 'HTH', 'Ethan', 'Yelle'); insert into Students_tbl values (10230, 'SCI', 'Bronwyn', 'Bradwell'); insert into Students_tbl values (10330, 'ENG', 'Charity', 'Bruhn'); insert into Students_tbl values (10140, 'SCI', 'Nathanial', 'Stroupe'); insert into Students_tbl values (10570, 'HTH', 'Adrianna', 'Dames'); insert into Students_tbl values (10830, 'SCI', 'Cody', 'Johnson'); insert into Students_tbl values (10240, 'SCI', 'Josefina', 'Martinez'); insert into Students_tbl values (10180, 'ENG', 'Kristin', 'Mccleery'); insert into Students_tbl values (10390, 'HST', 'Scott', 'Rambert'); insert into Students_tbl values (10660, 'SCI', 'Eleonore', 'Mossman'); insert into Students_tbl values (10810, 'HTH', 'Arnold', 'Hallman');
As you may imagine, comments can also help those who are exploring the concepts involved in using SQL. You may elect to use for detail in your comments for learning purposes. In your example above, most of the department values are relatively self-explanatory, with the exception of the English department, as “ENG” could have also been used for an Engineering department. Comments may also be used to give more information for the history of tables; such as, who had created the table, what the table is used for, and when the table is typically updated. Would you like to see more examples of SQL usage while learning more about using Oracle SQL? Take a Udemy class.
When using code to define objects, create objects, perform functions, etc.; adding comments is one of the methods used for best practices. Adding comments to code not only helps the author of code, but helps others who may read the code as well. I suggest that comments be as detailed as possible without including irrelevant information.