Skip to content

# SQL: good comments conventions

Development MySQL Coding SQL Syntax July 1, 2010

I happened upon a customer who left me in awe and admiration. The reason: excellent comments for their SQL code.

I list four major places where SQL comments are helpful. I’ll use the sakila database. It is originally scarcely commented; I’ll present it now enhanced with comments, to illustrate.

#### Table definitions

The CREATE TABLE statement allows for a comment, intended to describe the nature of the table:

>     CREATE TABLE `film_text` (
>      `film_id` smallint(6) NOT NULL,
>      `title` varchar(255) NOT NULL,
>      `description` text,
>      PRIMARY KEY (`film_id`),
>      FULLTEXT KEY `idx_title_description` (`title`,`description`)
>     ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Reflection of `film`, used for FULLTEXT search.'

It’s too bad the comment’s max length is 60 characters, though. However, it’s a very powerful field.

#### Column definitions

One may comment particular columns:

>     CREATE TABLE `film` (
>      `film_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
>      `title` varchar(255) NOT NULL,
>      `description` text,
>      `release_year` year(4) DEFAULT NULL,
>      `language_id` tinyint(3) unsigned NOT NULL COMMENT 'Soundtrack spoken language',
>      `original_language_id` tinyint(3) unsigned DEFAULT NULL COMMENT 'Filmed spoken language',
>      `rental_duration` tinyint(3) unsigned NOT NULL DEFAULT '3',
>      `rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99',
>      `length` smallint(5) unsigned DEFAULT NULL,
>      `replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99',
>       ...
>     ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8

#### Stored routines definitions

Here’s an original sakila procedure, untouched. It is already commented:

>     CREATE DEFINER=`root`@`localhost` PROCEDURE `rewards_report`(
>      IN min_monthly_purchases TINYINT UNSIGNED
>      , IN min_dollar_amount_purchased DECIMAL(10,2) UNSIGNED
>      , OUT count_rewardees INT
>     )
>      READS SQL DATA
>      COMMENT 'Provides a customizable report on best customers'
>     BEGIN

>      DECLARE last_month_start DATE;
>      DECLARE last_month_end DATE;
>      ...

#### SQL queries

Last but not least, while not part of the schema, SQL queries define the use of the schema. That is, the schema exists for the sole reason of being able to query it.

Where did that query come from? Which piece of code issued it? Why? What’s its purpose?

Looking at the PROCESSLIST, the slow log, etc., it is easier when the queries are commented:

>     SELECT
>      /* List film details along with participating actors */
>      /* Issued by analytics module */
>      film.*,
>      COUNT(*) AS count_actors,
>      GROUP_CONCAT(CONCAT(actor.first_name, ' ', actor.last_name))
>     FROM
>      film
>      JOIN film_actor USING(film_id)
>      JOIN actor USING(actor_id)
>     GROUP BY film.film_id;

#### Conclusion

Source code commenting is an important practice, and usually watched out for. SQL & table definitions commenting are often scarce or non-existent. I urge DBAs to adopt a comments coding convention for SQL, and apply it whenever they can.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
5
Leave a Reply

5 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
4 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Roland Bouman

It’s funny you should mention sakila and comments….way, way, waaaay back when Mike Hillyer created the database, I proposed to put all documentation in comments, and use an XML export in combination with an XSLT stylesheet to generate the schema documentation into a HTML format.

Alas, the comment fields are way to small to seriously use them for this purpose. Anyway…you reminded me of an inspiring past. Thanks!

Guest
Daniël van Eeden

It’s too bad that it’s not possible to add a comment to a CREATE DATABASE

Guest
Shantanu Oak

Apart from comments I do also use different cases like SeLeCt or selecT. Then I use ‘grep’ command to find the query from log file.

Author
shlomi

@Roland,
Glad I could bring up the past 🙂

@Shantanu,
sounds painful!

Comment your MySQL schema « Electricmonk.nl weblog

[…] SQL: good comments conventions […]

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.