SQL Comments
Introduction
SQL comments are explanatory notes placed inside SQL source code. The database parser recognizes comment delimiters and normally ignores the enclosed text when it interprets the statement. Comments can explain why a filter exists, record assumptions for a migration, describe an unusual join, identify a performance decision, or temporarily exclude code during controlled development.
A useful comment preserves context that the SQL itself cannot express clearly. It should help the next reader understand intent, risk, or history without repeating obvious syntax. A comment such as -- Select customer name above SELECT customer_name adds little value. A note explaining why test accounts are excluded or why historical prices must be used can prevent a future defect.
Comments are not executable safety controls, version control, or a substitute for meaningful names and readable formatting. They are supporting documentation embedded close to the code. Used carefully, they make queries and database changes easier to review and maintain. Used carelessly, they become stale, hide dangerous edits, disclose sensitive data, or create a false sense of safety.
Why SQL Comments Matter
SQL often contains business rules that are not visible from syntax alone. A filter may exclude internal customers, a calculation may use the price recorded at purchase time, or a join may intentionally retain unmatched rows. Without context, a well-meaning developer may simplify the statement and change its meaning.
-- Exclude internal test accounts from regulatory totals.
SELECT SUM(total_amount)
FROM orders
WHERE is_test_account = FALSE;
This comment explains the reason for the filter rather than translating the WHERE clause into English. Comments are particularly valuable in migrations, financial logic, data cleanup, compatibility workarounds, reporting queries, stored routines, and any code where an apparently unnecessary condition protects a business requirement.
Main SQL Comment Styles
The two most widely recognized SQL comment styles are the double-hyphen single-line comment and the slash-asterisk block comment. Some products support additional forms, but these two are the strongest starting point for portable scripts.
| Style | Syntax | Typical use |
|---|---|---|
| Single-line | -- explanation | Short note extending to the end of the line |
| Block | /* explanation */ | Multi-line explanation or a bounded inline note |
Comment text may contain ordinary language, ticket references, expected row counts, or usage instructions. Keep it concise enough to remain readable beside the statement it explains.
Single-Line Comments
A single-line comment begins with two hyphens and continues to the end of that physical line. SQL following the comment on the next line is parsed normally.
-- Return active customers only.
SELECT customer_id, customer_name
FROM customers
WHERE is_active = TRUE;
A comment can also follow code on the same line:
WHERE order_status = 'PAID' -- Refunds are handled separately.
Inline comments are useful for short qualifications, but placing many at the ends of long lines makes scripts difficult to scan. Put important business explanations on their own line directly above the affected clause.
Multiple Single-Line Comments
Longer explanations can use consecutive single-line comments. Each physical line must begin with the delimiter.
-- Revenue is based on the price captured on each order line.
-- Current catalog prices must not be used because they may
-- have changed after the customer completed the purchase.
SELECT SUM(quantity * purchase_unit_price)
FROM order_items;
This form is easy to edit line by line and avoids questions about nested block delimiters. For a short paragraph, however, a block comment may be more compact.
Block and Multi-Line Comments
A block comment begins with /* and ends with */. It can span multiple lines or appear within one line.
/*
Calculate totals from completed orders only.
Cancelled and test orders are intentionally excluded.
*/
SELECT SUM(total_amount)
FROM orders
WHERE order_status = 'COMPLETED'
AND is_test_order = FALSE;
A bounded inline block comment can identify a special expression:
SELECT total_amount * 0.20 /* standard tax rate */ AS tax_amount
FROM invoices;
Inline blocks should be used sparingly because they interrupt the code. A named rate table or configuration value is generally better than embedding a changing business constant with only a comment.
How the Parser Treats Comments
The parser removes or skips ordinary comments before interpreting the surrounding SQL. As a result, comments normally do not add result rows, change selected values, or alter predicates. They remain part of the source file but not the logical query.
SELECT customer_id, /* stable public identifier */ customer_name
FROM customers;
Comment delimiters are recognized according to the lexical rules of the database. Delimiter-like characters inside a quoted string are data, not comments:
SELECT '-- not a comment' AS sample_text,
'/* also text */' AS another_value;
Understanding that distinction is important when debugging dynamic SQL, migration scripts, or security issues. The parser must first determine whether characters occur in code, a quoted literal, a delimited identifier, or a comment.
Temporarily Disabling SQL
Developers often comment out a column, predicate, join, or statement while diagnosing a query. This can be useful in a local session, but the remaining SQL must still be syntactically valid and logically safe.
SELECT
customer_id,
customer_name
-- , phone_number
FROM customers;
Comma placement is a common failure. Commenting out the last selected expression is easier when commas are placed consistently. More importantly, disabling a predicate can increase the scope of a statement. A commented filter in a SELECT may return extra rows; the same action in an UPDATE or DELETE can modify every row.
Destructive SQL and Comment Risk
Never treat a commented line as protection for destructive SQL. The following pattern is dangerous because removing or commenting the predicate changes the operation from targeted to table-wide:
DELETE FROM orders;
-- WHERE order_status = 'CANCELLED';
The semicolon already completes the DELETE, so the commented predicate has no effect. Similar mistakes occur when experimenting with UPDATE statements. A safer workflow starts with a transaction and a validation query using the exact predicate:
BEGIN;
SELECT order_id, order_status
FROM orders
WHERE order_status = 'CANCELLED'
AND cancelled_at < CURRENT_DATE - INTERVAL '365 days';
-- Run the matching DELETE only after reviewing the selected rows.
-- Roll back first if the scope is unexpected.
ROLLBACK;
Transaction syntax varies by platform, and some schema operations commit automatically. Comments can remind an operator about checks, but permissions, backups, transactions, tested rollback procedures, and peer review provide the real safeguards.
Explain Why, Not What
The strongest comments describe intent, constraints, exceptions, or tradeoffs. The SQL already shows what operation occurs. Repeating it creates noise.
| Weak comment | Useful comment |
|---|---|
-- Join orders | -- Keep customers without orders for the retention report. |
-- Filter status | -- Include PAID only; refunds are reported by the settlement job. |
-- Calculate amount | -- Use purchase-time prices to preserve historical revenue. |
-- Sort descending | -- Resolve ties deterministically for cursor pagination. |
A useful test is to remove the comment and ask what important knowledge disappears. If the answer is nothing, improve the SQL or remove the comment.
Comments for Business Rules
Business rules are often the best reason for an SQL comment. They may come from contracts, finance policies, regulations, or domain decisions that cannot be inferred from column names.
-- A subscription remains billable through the end of its paid period,
-- even when cancellation was requested earlier.
WHERE paid_through_date >= CURRENT_DATE
The comment should describe the stable rule, not copy a meeting discussion. If the rule changes frequently or requires extensive explanation, link the migration or repository documentation to an authoritative requirement and keep a concise summary near the SQL.
Comments for Complex Calculations
Calculations involving tax, money, time zones, percentages, rounding, or historical values deserve context. Explain input assumptions and units, especially when two plausible formulas would produce different results.
-- Round each line before summing because invoices are settled
-- at line level, not by rounding the final invoice total.
SELECT invoice_id,
SUM(ROUND(quantity * unit_price, 2)) AS invoice_total
FROM invoice_items
GROUP BY invoice_id;
A comment does not compensate for a vague name. Expressions should still use meaningful aliases, and complex calculations may belong in a tested view, function, or application service.
Comments for NULL and Join Logic
SQL's three-valued logic and join semantics can surprise readers. A concise note can protect intentional handling of NULL or unmatched records.
-- NULL means no expiration date, so the record remains active.
WHERE expires_at IS NULL OR expires_at > CURRENT_TIMESTAMP
-- LEFT JOIN is intentional: customers with no orders must appear.
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
Explain the business interpretation of missing data rather than teaching basic IS NULL syntax. If a join condition is non-obvious, state the relationship and expected cardinality.
Comments for Pagination and Aggregation
Pagination queries should document deterministic ordering, cursor semantics, or compatibility requirements. Aggregations should explain grain, exclusions, and whether values are calculated before or after grouping.
-- order_id breaks timestamp ties so pages do not skip or repeat rows.
ORDER BY created_at DESC, order_id DESC
-- One result row per customer and calendar month in UTC.
GROUP BY customer_id, DATE_TRUNC('month', ordered_at)
These notes prevent refactoring that removes an apparently redundant sort key or changes reporting grain.
Performance Decisions and Workarounds
When SQL contains a workaround, unusual join order, redundant-looking predicate, or denormalized expression for a measured reason, document the evidence and scope. Include a ticket or benchmark reference when available.
-- Keep this predicate aligned with idx_orders_customer_created.
-- Removing customer_id causes a full scan in the monthly export.
WHERE customer_id = :customer_id
AND created_at >= :start_time
Performance comments can become outdated as data distribution, statistics, indexes, and database versions change. State when the decision was measured and review it when the query or schema changes. Do not preserve poor SQL indefinitely merely because an old comment claims it was faster.
Comments Around Indexes
An index definition can include a short note describing the workload it supports and any operational cost:
-- Supports customer order history sorted by newest first.
-- Recheck write overhead if order volume grows substantially.
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);
The catalog definition and execution plans remain the technical source of truth. The comment records the design intent, helping reviewers decide whether an index is still needed before removal.
Comments in Table Definitions
Schema scripts may place comments near columns or constraints whose meaning is not obvious:
CREATE TABLE payments (
payment_id BIGINT PRIMARY KEY,
amount DECIMAL(12, 2) NOT NULL,
currency_code CHAR(3) NOT NULL,
-- Timestamp reported by the payment provider, stored in UTC.
provider_processed_at TIMESTAMP,
-- Prevent negative settlements; refunds use separate records.
CONSTRAINT ck_payments_amount_positive CHECK (amount >= 0)
);
For durable object documentation that must be visible through database metadata, use the platform's supported metadata-comment or extended-property feature rather than relying only on comments in a migration file.
Script Comments and Metadata Comments
Script comments live in source code and disappear from the executed definition unless the database stores the source text. Metadata comments are attached to database objects and can be retrieved through catalog tools. Several systems support statements such as COMMENT ON, while others use descriptions or extended properties.
COMMENT ON COLUMN orders.total_amount IS
'Final order amount in the currency identified by currency_code';
Syntax and support vary by database. Metadata comments are appropriate for stable definitions, ownership, units, classifications, and domain meaning. Script comments are better for migration sequence, operational instructions, and reasons specific to a change.
Migration File Headers
A migration header should state purpose, prerequisites, compatibility assumptions, verification steps, and rollback limitations. It should not duplicate version-control history or contain personal notes.
/*
Purpose: add customer_id to historical invoice exports
Prerequisite: customers.customer_id backfill completed
Expected impact: metadata lock during column creation
Validation: zero invoices with a missing customer reference
Rollback: remove dependent view before dropping the column
*/
A clear header helps operators and reviewers understand the change before reading implementation details. Keep it synchronized with the actual script and deployment process.
Preconditions, Postconditions, and Expected Counts
Data correction scripts should identify what must be true before execution and how success will be checked afterward.
-- Precondition: duplicate active emails have been resolved.
-- Expected update: approximately 18,000 legacy customer rows.
UPDATE customers
SET email_address = LOWER(TRIM(email_address))
WHERE email_address <> LOWER(TRIM(email_address));
-- Postcondition: no email contains uppercase letters or edge spaces.
SELECT COUNT(*) AS invalid_email_count
FROM customers
WHERE email_address <> LOWER(TRIM(email_address));
Expected counts are estimates, not controls. The script should stop or alert through deployment tooling when validation fails. A comment alone cannot enforce the expectation.
Irreversible and High-Risk Operations
When a migration drops data, rewrites a large table, changes precision, or performs another difficult-to-reverse operation, label the risk clearly and describe recovery requirements.
-- IRREVERSIBLE after backup expiration:
-- removes raw provider payloads older than the retention period.
DELETE FROM payment_provider_payloads
WHERE received_at < :retention_cutoff;
Operational procedures should require approval, verified backups, bounded batches, observability, and tested recovery. The comment draws attention to the risk but does not make the operation safe by itself.
Comments in Stored Procedures and Functions
Stored code benefits from a brief header describing purpose, inputs, outputs, transaction behavior, side effects, and important exceptions. Within the body, comments should explain branches that implement business rules or compatibility behavior.
-- Applies one payment to an open invoice.
-- Caller owns the transaction; this routine does not commit.
-- Raises an error when payment currency differs from invoice currency.
Avoid narrating every assignment or control structure. Clear routine names, parameter names, local variables, and small units of logic reduce the need for extensive comments.
Comments in Triggers
Triggers execute indirectly, so their purpose and side effects should be especially clear. Explain why the trigger exists, which operations activate it, what it writes, and whether recursion or bulk operations require special handling.
-- Records status transitions for audit reporting.
-- Does not store updates where order_status is unchanged.
Comments should not hide surprising behavior. Trigger naming, documentation, tests, and monitoring should make the automation discoverable to people who did not author it.
Comments for CTEs and Complex Query Stages
A common table expression should have a meaningful name. Comments can explain the grain or reason for a transformation when that is not evident.
WITH ranked_orders AS (
-- Rank within each customer so rn = 1 is the latest order.
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY ordered_at DESC, order_id DESC
) AS rn
FROM orders AS o
)
SELECT *
FROM ranked_orders
WHERE rn = 1;
Do not use comments to compensate for CTE names such as cte1 and cte2. Name each stage for the result it produces, then comment only the non-obvious rule.
Comments and Optimizer Hints
Some database products encode optimizer hints using syntax that resembles a block comment. Such hints are not ordinary documentation: supported engines may interpret them and change execution behavior. For example, a platform may recognize a specially marked comment immediately after a statement keyword.
SELECT /*+ platform_specific_hint */ customer_id
FROM customers;
Hint syntax and semantics differ substantially across products. SQL Server commonly exposes query and table hint syntax that is not simply an ordinary comment. Treat all hints as executable, product-specific code. Measure the need, document the reason, test upgrades, and do not assume that copying a comment-shaped hint into another database is harmless or effective.
Comments and Execution Plans
Ordinary comments generally do not change relational results, but query text can participate in plan caches, diagnostics, monitoring, and statement fingerprints. A changing request identifier embedded as a comment may produce noisy observability or extra statement variants on some systems and tools.
Use structured tracing, session context, application names, or supported telemetry mechanisms when available. A stable comment identifying a query family can help operational analysis, but high-cardinality values such as a unique request ID should not be inserted casually into every SQL string.
Generated SQL
Generated SQL may include a stable comment indicating the generator, version, or source feature. This helps distinguish framework output from hand-written statements during debugging.
/* generated by monthly-revenue-report v3 */
SELECT customer_id, SUM(total_amount)
FROM orders
GROUP BY customer_id;
Do not inject untrusted values into comments. User input containing delimiters or unexpected characters can break the intended lexical structure. Parameterization protects data values, while comments and identifiers require trusted, controlled construction.
Comments and SQL Injection
Attackers may use SQL comment markers to truncate the remainder of an unsafe dynamically constructed statement. A login query built by concatenating input can be changed so trailing checks become comments. The exact payload depends on the database and surrounding SQL, but the defensive lesson is universal: never assemble data values by string concatenation.
-- Unsafe pattern: user input changes the SQL grammar.
query = "SELECT * FROM users WHERE username = '" + username
+ "' AND password_hash = '" + passwordHash + "'";
Use parameterized statements, least-privilege accounts, allowlisted dynamic identifiers, and security testing. Do not attempt to block injection merely by removing comment characters; attackers have many ways to alter unsafe SQL.
Never Store Secrets in Comments
SQL files are copied into repositories, build logs, deployment artifacts, backups, ticket systems, and chat messages. Comments must never contain passwords, private keys, access tokens, production connection strings, personal data, or unredacted incident details.
Replace secrets through the organization's secret-management process and reference a safe configuration key or runbook. If a secret has already been committed in a comment, deleting the current line is not sufficient because version history and copied artifacts may still contain it. Revoke and rotate the exposed credential.
TODO and FIXME Comments
A TODO can identify planned work, and a FIXME can flag known incorrect or fragile behavior. Both should include an owner or tracked issue and a condition for removal.
-- TODO DB-482: remove legacy status mapping after all clients use v2 codes.
-- FIXME DB-511: this fallback misclassifies records without a source timestamp.
Untracked reminders accumulate and become permanent noise. High-risk defects should not remain only as comments; they need issue tracking, prioritization, tests, and operational visibility.
Portable Comment Syntax
Double-hyphen line comments and slash-asterisk block comments are broadly supported by major relational databases. Product-specific alternatives exist. MySQL, for example, also recognizes hash-style comments in relevant contexts and applies specific lexical rules to double-hyphen comments. Portable scripts should prefer the shared forms and test against every supported engine.
| Database family | Broadly used forms | Portability note |
|---|---|---|
| PostgreSQL | --, /* */ | Use standard forms for shared scripts |
| SQL Server | --, /* */ | Hints commonly use separate product syntax |
| Oracle Database | --, /* */ | Special hint comments can affect optimization |
| MySQL | --, /* */, product forms | Double-hyphen lexical details matter |
| SQLite | --, /* */ | Standard forms are the portable choice |
Nested Block Comments
Support for nested block comments differs among database products and tools. Even where nesting is accepted, a script containing one block comment cannot always be safely wrapped in another block comment. An inner closing delimiter may terminate the outer block unexpectedly.
When temporarily disabling code that already contains block comments, prefer line comments applied by the editor or move the code into a separate scratch file. Never assume nested behavior is portable.
Editor and GUI Shortcuts
SQL editors commonly provide shortcuts to comment or uncomment selected lines. The shortcut usually adds or removes line delimiters, which is safer for a selection containing block comments. Exact keys differ among tools and operating systems.
After using a shortcut, review the selection boundaries, delimiters, commas, statement terminators, and predicates. Tool assistance changes text quickly but cannot verify business meaning or destructive scope.
Formatting Long SQL Files
Section headers can make a long learning, deployment, or maintenance script easier to navigate:
-- ============================================================
-- 1. PRECONDITION CHECKS
-- ============================================================
-- ============================================================
-- 2. SCHEMA CHANGE
-- ============================================================
-- ============================================================
-- 3. DATA VALIDATION
-- ============================================================
Keep decoration restrained and consistent. Very large banners, repeated changelogs, or paragraphs of prose can obscure the executable code. Repository documentation is better for architecture, runbooks, and long historical explanations.
File Header Comments
A useful file header identifies purpose, major assumptions, execution order, compatibility, and verification. Avoid manually maintained lists of every person and change; version control already records authorship and history more accurately.
Personal notes such as "ask Sam before running" are fragile when roles change. Replace them with a team, approval workflow, runbook, or issue reference that remains maintainable.
Comments Versus Documentation
Comments should stay close to local logic. Architecture decisions, entity definitions, recovery procedures, ownership, and operational playbooks belong in durable documentation. Metadata descriptions belong in the database catalog when users need to discover them through tools.
The layers complement each other. A query comment can summarize why a condition exists and link to a design decision. The linked document can explain alternatives, evidence, and broader context. Repeating the full document inside every query makes updates difficult.
Comments Versus Clear Naming
A comment should not be required to decode vague identifiers. Replace x, dt, and val with names such as customer_id, ordered_at, and total_amount. Then use comments for business meaning the identifiers cannot carry.
-- Poor: comment compensates for unclear names.
SELECT x, dt, val FROM t; -- customer, order date, total
-- Better: names explain the data; comment explains the rule.
-- Total uses the settled amount after approved adjustments.
SELECT customer_id, order_date, settled_total
FROM completed_orders;
Keep Comments Current
An outdated comment is often worse than no comment because readers may trust it. Whenever code changes, review nearby comments, file headers, metadata descriptions, expected counts, ticket references, and rollback instructions. Remove a workaround comment when the workaround is removed.
Code review should treat comments as part of the implementation. Verify that they describe current behavior, use clear language, avoid secrets, and explain the right level of context. Comments should evolve in the same commit or migration as the SQL they describe.
Common Commenting Mistakes
Too many comments make simple SQL difficult to scan, while too few leave complex business decisions unexplained. Wrong or stale comments actively mislead. Permanent debug notes, personal reminders, secrets, and commented-out obsolete code create clutter and risk.
Syntax mistakes are also common. Commenting out only one side of a comma, a join condition, a parenthesis, or a statement terminator can break parsing or silently change results. A disabled ON condition can turn a relationship into a Cartesian product. Always execute a safe validation query and inspect row counts after editing structural SQL.
A Practical Commenting Workflow
Begin by writing readable SQL with meaningful identifiers, explicit structure, and consistent formatting. Then identify knowledge that is not visible from the statement: business intent, unusual assumptions, safety checks, compatibility constraints, measured performance decisions, and migration sequencing.
Add the shortest comment that preserves that knowledge. Place it immediately above the relevant clause or in a concise file header. Link to a tracked issue or design document when the explanation is too large. Test the SQL with comments present, because delimiter placement can affect parsing. Finally, review the comment during every code change and remove it when it no longer adds value.
Real-World Example
/*
Monthly recognized revenue export.
Grain: one row per customer and UTC calendar month.
Rule: use line-item prices captured at purchase time.
*/
WITH eligible_orders AS (
SELECT order_id, customer_id, ordered_at
FROM orders
WHERE order_status = 'PAID'
AND is_test_order = FALSE -- Internal test orders are never recognized.
), monthly_revenue AS (
SELECT
eo.customer_id,
DATE_TRUNC('month', eo.ordered_at) AS revenue_month,
SUM(oi.quantity * oi.purchase_unit_price) AS revenue_amount
FROM eligible_orders AS eo
JOIN order_items AS oi
ON oi.order_id = eo.order_id
GROUP BY eo.customer_id, DATE_TRUNC('month', eo.ordered_at)
)
SELECT customer_id, revenue_month, revenue_amount
FROM monthly_revenue
ORDER BY revenue_month, customer_id;
The header defines purpose and grain, the inline note protects a business exclusion, and the CTE names describe each stage. The comments add context without narrating every keyword.
Practical Review Checklist
- Use double-hyphen or block comments according to the supported databases.
- Explain intent, assumptions, risks, and tradeoffs rather than obvious syntax.
- Keep comments directly beside the SQL they describe.
- Use metadata descriptions for durable object and column documentation.
- Validate destructive statements with transactions, previews, and safeguards.
- Treat optimizer hints as executable product-specific code.
- Never place credentials, tokens, personal data, or untrusted input in comments.
- Track TODO and FIXME work outside the comment as well.
- Avoid relying on nested block comments in portable scripts.
- Review delimiters, commas, joins, and predicates after commenting out code.
- Update comments whenever the related SQL changes.
- Delete comments that are obsolete, misleading, or merely repeat the code.
Interview Questions and Answers
What is an SQL comment? It is non-executable explanatory text recognized by SQL comment delimiters and normally ignored when the statement is parsed.
What are the main SQL comment syntaxes? Double hyphens introduce a line comment, while /* and */ delimit a block comment.
What should a good comment explain? It should explain why logic exists, the business rule, important assumptions, safety requirements, or a non-obvious tradeoff.
Are comments a safety mechanism? No. Permissions, transactions, backups, validation, review, and tested recovery procedures provide safety.
Can comments affect optimization? Ordinary comments generally do not express query logic, but some products use special comment-shaped optimizer hints that are executable instructions.
Why are comments relevant to SQL injection? Attackers can use comment markers to truncate unsafe concatenated SQL. Parameterized statements are the primary defense for data values.
Are nested block comments portable? No. Support differs among products and tools, so portable scripts should avoid depending on nesting.
What is the difference between a script comment and a metadata comment? A script comment documents source code, while a metadata comment is attached to an object and exposed through the database catalog.
Key Takeaway
SQL comments preserve intent that cannot be expressed clearly through syntax and naming alone. Use -- for focused line notes and /* ... */ for bounded or multi-line explanations. Prefer the broadly supported forms when scripts must run on several databases.
GOOD SQL COMMENT =
IMPORTANT CONTEXT + CLEAR REASON + CURRENT INFORMATION
Write readable SQL first, then comment the business rule, assumption, risk, or measured decision. Keep comments current, free of secrets, and close to the relevant code. Never confuse a comment with a safety control, and always test the complete statement after commenting or uncommenting SQL.