Writing T-SQL Somebody Else Can Maintain

Writing maintainable T-SQL means leaving the next reader enough information to change it safely. Clear intent matters more than clever compression or a perfectly aligned wall of keywords.

Name the Business Meaning

Names should explain what a value represents, not merely its storage type. InvoiceDate and PaidAt describe different events even if both use a date-related type. Calling both DateValue saves typing and spends everyone else’s attention.

Use aliases that remain understandable in the surrounding query. A short alias is fine when its meaning is obvious. In a long statement, descriptive aliases can prevent repeated trips back to the FROM clause.

WITH Invoices AS
(
    SELECT * FROM (VALUES
      (101, CONVERT(date, '20260110', 112), CAST(125 AS decimal(12,2))),
      (102, CONVERT(date, '20260120', 112), CAST(80 AS decimal(12,2)))
    ) AS v(InvoiceId, InvoiceDate, Amount)
)
SELECT InvoiceId, InvoiceDate, Amount
FROM Invoices
ORDER BY InvoiceDate, InvoiceId;

Keep units visible in names when ambiguity is likely. TimeoutSeconds is easier to use correctly than Timeout. The same principle applies to currencies, local dates, and UTC timestamps.

Make the Result Shape Deliberate

List returned columns in code that other components consume. Give calculated columns meaningful names and document any required ordering. A result set is an interface, even when nobody wrote an interface document.

Do not depend on an index to return rows in a preferred sequence. Use ORDER BY when order matters, with a tie-breaker when needed. Keep the rule consistent with what the caller actually expects.

Avoid using one result column for several unrelated meanings across branches. If the caller must guess the shape from a status flag, the procedure is carrying hidden complexity. A clearer contract usually reduces both SQL and application work.

Express One Rule Over a Set

When every eligible row receives the same kind of change, describe the set and the rule. A loop can obscure that relationship. It can also create extra round trips or repeated work.

DECLARE @Invoices table
(
    InvoiceId int PRIMARY KEY,
    DueDate date NOT NULL,
    PaidAt datetime2 NULL,
    NeedsReminder bit NOT NULL
);
INSERT @Invoices VALUES
 (101, '20260110', NULL, 0), (102, '20260210', NULL, 0);
DECLARE @AsOf date = '20260201';
UPDATE @Invoices
SET NeedsReminder = 1
WHERE PaidAt IS NULL AND DueDate < @AsOf;
SELECT InvoiceId, NeedsReminder FROM @Invoices;

The example uses an explicit reference date so the rule can be tested repeatedly. Production code still needs a decision about where that date comes from. A clock dependency hidden inside several expressions is harder to reproduce.

Set-based work can still require batching for operational reasons. Large changes affect locks, logging, and recovery time. Clarity about the rule and control over the transaction are complementary goals.

Write Comments That Explain Why

A comment saying increment counter usually repeats the code. A comment explaining a contractual grace period preserves knowledge the code cannot express alone. Record the reason for an unusual boundary or exception.

DECLARE @AsOf date = '20260201';
-- The reminder policy gives invoices seven full calendar days after the due date.
DECLARE @ReminderCutoff date = DATEADD(day, -7, @AsOf);
SELECT @ReminderCutoff AS reminder_cutoff;

Keep the comment close to the decision it explains. If the policy changes, update both together. An outdated explanation can be more misleading than no explanation at all.

Do not fill comments with a running history of every edit. Keep a separate change record when that history matters. The code should describe its current purpose without making the reader reconstruct a timeline.

Give Each Procedure a Clear Responsibility

A procedure that imports data, rebuilds indexes, sends mail, and creates reports has several reasons to change. Separate responsibilities where the boundaries are useful. Do not split a cohesive transaction into fragments merely to shorten files.

State who owns the transaction and how errors reach the caller. Keep output parameters, return codes, and result sets purposeful. Swallowing an error and returning an empty result makes operational failures look like valid business answers.

Review dynamic SQL with particular care. Values belong in parameters, while object names require validation and identifier quoting. A clean-looking string is not a substitute for a clear execution contract.

Leave a Repeatable Example

Save a small test case covering normal input, missing input, and a boundary condition. Use synthetic data that another developer can recreate. Explain expected behavior without depending on a production snapshot.

Before handing over a change, ask another reader to describe the query’s purpose. Any disagreement is useful feedback about names or assumptions. Maintainability is partly measured by how little private explanation the next person needs.

Maintainable SQL is not decorative formatting, it is shared understanding made durable.

This post was rewritten from scratch in September 2026. The original, published on 2007-09-15, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.