SQL Comment: A Comprehensive Guide

intro

SQL is the cornerstone of modern database management, enabling users to create, manipulate, and retrieve data with precision and efficiency. Among the multitude of features SQL offers, comments hold a special place for their role in enhancing code readability and maintenance. Add an SQL comment to your query and your team entire will thank you!

This article will dig into the art of commenting in SQL, discussing the how and why, the pros and cons, and the subtle nuances across various database engines.

Become an SQL comment expert!

Understanding SQL Comments

An SQL comment is an annotation in the code that is not executed by the SQL engine. Just like in programming languages, SQL comments serve as notes or explanations for the human reader, providing clarity and context to the given query.

SQL supports two primary types of comments:

  1. Single-line comments: They begin with -- and are followed by the comment text.
  2. Multi-line comments: They are enclosed between /* and / and are suitable for longer annotations or temporarily disabling code blocks.

Let’s now see how to write an SQL comment!

How to Comment in SQL

As mentioned before, single-line comments are made using --:

Copy
        
1 -- This is a comment to explain what the following SELECT query does 2 SELECT * FROM users;

As a best practice, it is recommended to follow -- with a space to ensure clear separation from the comment text. This enhances readability and prevents potential parsing issues across different SQL environments.

Multi-line comments use /* to start and */ to end:

Copy
        
1 /* 2 A long comment that takes multiple lines 3 because it aims to describe in detail what happens 4 in the query 5 */ 6 SELECT * FROM customers;

These are ideal for longer notes or for commenting out sections of code during debugging.

Pros and Cons of Commenting in SQL

Explore the main benefits and drawbacks of commenting in SQL.

👍 Pros:

  • Comments make SQL scripts easier to understand, facilitating quicker reviews and onboarding of new team members.
  • Well-commented code is easier to maintain, debug, and update.
  • SQL comments act as in-line documentation, explaining the purpose of complex queries or decisions made during development.

👎 Cons:

  • Over-commenting can make code harder to navigate.
  • Outdated or inaccurate comments can mislead developers, causing confusion and errors.
  • Sensitive information accidentally left in an SQL comment can pose security risks.

Nuances of SQL Comments Across Different Database Engines

Different SQL database engines, such as MySQL, PostgreSQL, SQLite, SQL Server, and Oracle, generally support the standard SQL comment syntax. However, there are some engine-specific considerations to keep in mind. Time to delve into them!

Commenting in MySQL

MySQL supports both standard single-line (-- with a space after --) and multi-line (/* ... */) comments. An interesting feature in MySQL is the ability to include version-specific comments that allow queries to behave differently depending on the MySQL version being used. This can be particularly useful for maintaining backward compatibility or leveraging new features in newer versions without breaking your queries on older systems.

Commenting in PostgreSQL

PostgreSQL also adheres to the standard commenting syntax but also introduces other features. In detail, database objects stored in a database can have a dedicated text comment. These comments are created through the COMMENT ON statement.

This enables you to annotate tables, columns, functions, and other objects with descriptive text, which can be queried via PostgreSQL's system catalog. As you can imagine, such a feature is invaluable for documentation purposes and helps maintain a self-describing database schema.

Commenting in SQLite

SQLite supports the basic -- and /* ... */ comments, see their documentation. It does not offer advanced commenting features found in some other databases, but that reflects its straightforward nature.

This way, SQLite ensures that SQL scripts remain portable and easy to understand, without the overhead of additional commenting functionalities that might complicate script execution or portability.

Commenting in SQL Server

SQL Server supports standard SQL comment syntax and adds the capability to include comments within stored procedures and functions, which can be particularly useful for explaining complex logic or business rules implemented within these database objects.

Commenting in Oracle

Similar to PostgreSQL, Oracle Database supports the conventional SQL comment implementations and extends this functionality through the COMMENT ON statement. This gives you the ability to attach explanatory text to database objects within the database schema itself.

Oracle's implementation also supports querying these comments through data dictionary views, making it possible to extract and report on this embedded documentation. Additionally, it supports conditional SQL comments that can be used to include or exclude specific portions of PL/SQL code during execution, offering flexibility in how code is written and maintained.

Best Practices for Adding an SQL Comment

  • Use comments judiciously to explain "why" something is done, rather than "what" is being done.
  • Keep comments up-to-date with code changes to avoid confusion.
  • Avoid placing sensitive information in comments.
  • Learn the keyboard shortcuts in your Database Client or SQL tool. The popular client DbVisualizer for example has easy key bindings you can use for commenting both single lines and blocks. This makes it easier to write an SQL command in your query.

Conclusion

Comments are a vital part of writing clean, maintainable SQL code. They enhance the readability and documentation of your scripts, making it easier for others (and your future self) to understand the logic behind your data manipulations. By following the guidelines and practices outlined in this article, you can make the most of SQL comments to improve your database projects.

FAQ

How do you write an SQL comment in a query?

To comment in SQL, use -- for single-line comments (preferably with a space after -- for clarity) and /* ... */ for multi-line comments.

What does /* do in SQL?

The /* marks the beginning of a multi-line comment, which is ended with */.

Can you comment out two or more rows in an SQL query?

Yes, by using multi-line comment syntax /* at the start of the first line and */ at the end of the last line.

Dbvis download link img
About the author
TheTable
TheTable

The Table by DbVisualizer is where we gather together to learn about and simplify the complexity of working with database technologies.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

Glossary of the SQL Commands You Need to Know

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 12 min 2024-04-11
title

SUBSTRING_INDEX in SQL Explained: A Guide

author Lukas Vileikis tags MySQL SQL 8 min 2024-04-08
title

SQL NOT IN: the Good, Bad & the Ugly

author Lukas Vileikis tags MySQL SQL 7 min 2024-04-04
title

SQL Add to Date Operations: A Complete Guide

author Antonello Zanini tags DATE DATETIME MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-04-01
title

SQL CAST Function: Everything You Need to Know

author Antonello Zanini tags CAST MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-03-28
title

PostgreSQL Upsert: INSERT ON CONFLICT Guide

author Antonello Zanini tags POSTGRESQL UPSERT 7 min 2024-03-25
title

Unlocking the Power of CTEs in SQL

author Ochuko Onojakpor tags 9 min 2024-03-21
title

INSERT INTO SQL Clause

author Leslie S. Gyamfi tags INSERT SQL 6 min 2024-03-18
title

Enhancing Business Operations with Visualization Tools

author TheTable tags Data Visualization Tools 3 min 2024-03-15
title

Postgres TEXT vs VARCHAR: Comparing String Data Types

author Antonello Zanini tags POSTGRESQL TEXT VARCHAR 6 min 2024-03-14

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗