Building a Reference You Will Actually Use Again

A personal reference earns its place when it helps you solve a problem without repeating the entire investigation. Keep fewer examples with clear context, rather than a large folder of unexplained scripts.

Keep the Problem With the Script

Name each entry after the question it answers. A file called find-current-blocking.sql is easier to recognize than useful-query-final.sql. Use stable descriptive names and keep dated copies when behavior changes.

Start the note with the symptom, the question, and the scope of the answer. Include the source reference and why you kept the example. A script without those details can be correct and still be misapplied.

Organize by recurring problems such as recovery, access, performance, and data quality. Add version tags where they matter. Avoid a deep folder tree that requires remembering where you originally found the idea.

Record Prerequisites and Side Effects

-- Purpose: identify the current SQL Server and database.
-- Scope: current connection; no configuration changes.
SELECT @@SERVERNAME AS server_name,
       DB_NAME() AS database_name,
       SERVERPROPERTY('ProductVersion') AS product_version,
       SERVERPROPERTY('Edition') AS edition,
       SYSUTCDATETIME() AS observed_at_utc;

A short header should identify required context, permissions, parameters, and whether the script changes anything. State when it creates persistent objects or generates significant work. Do not make a reader discover those facts halfway through execution.

Separate diagnostic queries from repair commands. A note can explain the relationship without placing an automatic repair after a SELECT. The person using the reference needs a visible decision point.

Keep secrets, customer data, and private connection details out of reusable examples. Replace them with clear placeholders or synthetic data. Record sensitive investigation evidence in its approved location instead.

Keep an Example Small Enough to Understand

Prefer a focused query that answers one question. Include the interpretation of its important columns and the limits of its coverage. A complicated diagnostic script can still belong in the reference when its purpose is clear.

SELECT session_id, status, blocking_session_id,
       wait_type, wait_time, total_elapsed_time,
       DB_NAME(database_id) AS database_name
FROM sys.dm_exec_requests
WHERE session_id <> @@SPID
ORDER BY total_elapsed_time DESC;

For this example, note that it shows active requests at one moment. It does not provide a complete history or list every sleeping blocking session. Visibility into other sessions also requires appropriate permission.

Include one sentence about the next check when the query finds something relevant. That preserves the reasoning without building a giant automatic troubleshooting chain. A reference should support judgment rather than conceal it.

Save the Validation Context

SELECT name, compatibility_level, collation_name,
       is_read_committed_snapshot_on
FROM sys.databases
WHERE database_id = DB_ID();
SELECT name, value
FROM sys.database_scoped_configurations
WHERE name IN
(N'LEGACY_CARDINALITY_ESTIMATION', N'MAXDOP');

Save relevant version and configuration details alongside a test result. Include the date, expected behavior, and what you actually observed. Mark an unexecuted example as unexecuted rather than letting a polished note imply validation.

Do not keep only a screenshot of successful output. Preserve the setup and the statement that produced it. A repeatable example remains useful when you need to check a new release or different data.

If the example depends on a table's shape, include synthetic setup data. Keep the original problem's important relationships without retaining private records. The reference should be safe to reuse in a disposable lab.

Review the Collection on a Schedule

Review frequently used entries after major platform changes and give the wider collection a periodic pass. An annual review is a useful starting routine. High-impact scripts deserve attention sooner when their dependencies change.

Check documentation links, supported syntax, permissions, and output interpretation. Mark superseded entries clearly instead of leaving several apparently current copies. Preserve useful historical context without allowing it to masquerade as current guidance.

SELECT name, type_desc, create_date, modify_date
FROM sys.objects
WHERE is_ms_shipped = 0
ORDER BY modify_date DESC;

This database query can support a specific object-review note, but it does not validate a local script library. Explain that distinction in the entry. Useful references are explicit about what their evidence can and cannot establish.

Make Retrieval Part of the Test

Ask whether you could find the entry from the symptom alone six months later. Add common search terms in the note when needed. A technically excellent example that cannot be found has little practical value.

Keep a short index of the entries used most often. Back up the local reference and preserve a simple change record. The collection should become easier to trust as it grows, not harder to understand.

A personal reference is not an archive of copied code, it is a collection of explained and maintained answers.

This post was rewritten from scratch in September 2026. The original, published on 2012-06-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.