Asking a SQL Question That Gets Answered: The Reproducible Script

Give another reader something they can run, and the SQL question becomes clearer. A reproducible script supplies the schema, representative inputs, query, and expected result without exposing real data.

State the Result Contract First

Describe the operation in plain language before pasting SQL. Identify what one output row represents, which rows must appear, how NULLs and duplicates are treated, and what ordering is required. A query can be syntactically correct while answering a different question. In a reproducible script, the expected result is the clearest way to make that difference visible.

Avoid starting with a screenshot of a long error or an entire production procedure. Text can be searched, copied, and executed. A screenshot can support a visual issue, but it cannot supply the runnable inputs behind a query. I begin with the smallest contract that still expresses the problem, then build the sample around it.

Name the boundary case that matters. An unmatched customer, duplicate key, NULL value, midnight timestamp, or tied ranking value can explain the failure more clearly than a large collection of ordinary rows. Keep those cases deliberate. An oversized sample gives the reader more data to scroll past without necessarily providing more evidence.

Put Only the Required Schema in the Reproducible Script

Temporary tables work well for a self-contained example that does not need persistent objects. Include actual data types, nullability, and key constraints when they affect the problem. The following sample asks for every customer and the total of paid orders, including zero for customers with no paid order.

CREATE TABLE #Customers
(
    CustomerID int NOT NULL PRIMARY KEY,
    CustomerName nvarchar(30) NOT NULL
);
CREATE TABLE #Orders
(
    OrderID int NOT NULL PRIMARY KEY,
    CustomerID int NOT NULL,
    PaymentState varchar(10) NOT NULL,
    Amount decimal(10,2) NOT NULL
);
INSERT #Customers(CustomerID,CustomerName)
VALUES(1,N'North'),(2,N'South'),(3,N'West');
INSERT #Orders(OrderID,CustomerID,PaymentState,Amount)
VALUES(101,1,'Paid',30.00),(102,1,'Pending',20.00),
      (103,2,'Pending',15.00);

These names and amounts are invented test inputs, not copied customer records. Customer 1 has a paid order, customer 2 has only a pending order, and customer 3 has no orders. Each row has a reason to exist. The example preserves the distinction between no matching row and a row that does not satisfy the payment condition.

Keep unrelated address columns, triggers, and indexes out until evidence shows they matter. If the problem involves a constraint or trigger, include the smallest valid version of that feature. Removing a relevant dependency makes the sample simpler but no longer representative, which defeats its purpose.

Show the Query That Produces the Wrong Shape

Include the exact failing query as text and describe its result. Here the filter sits in WHERE, after the outer join has produced its rows. Customers without a paid joined row are removed rather than retained with a zero total.

SELECT c.CustomerID,SUM(o.Amount) AS PaidTotal
FROM #Customers AS c
LEFT JOIN #Orders AS o ON o.CustomerID=c.CustomerID
WHERE o.PaymentState='Paid'
GROUP BY c.CustomerID
ORDER BY c.CustomerID;

For the supplied synthetic inputs, that expression returns customer 1 with 30.00 and omits the other customers. The requested contract needs all three customers. State that difference directly rather than asking why SQL Server is wrong. The engine can correctly execute a query whose logic does not match the author's intention.

If the issue is an error, include its full text, number, and the statement that raises it. If it is a performance problem, include the relevant plan and measurements from an approved test, plus the conditions under which they were captured. Do not substitute estimated timing or a dramatic description for observed evidence.

Make the Expected Rows Unambiguous

Show the expected values and explain why they follow the requirement. The following literal row set is the target for this sample, not a measurement from an unrelated database. Move the paid-order predicate into the join and handle the absent aggregate value explicitly.

SELECT CustomerID,PaidTotal
FROM (VALUES(1,CONVERT(decimal(10,2),30.00)),
            (2,CONVERT(decimal(10,2),0.00)),
            (3,CONVERT(decimal(10,2),0.00))) AS e(CustomerID,PaidTotal)
ORDER BY CustomerID;
SELECT c.CustomerID,COALESCE(SUM(o.Amount),0.00) AS PaidTotal
FROM #Customers AS c
LEFT JOIN #Orders AS o
    ON o.CustomerID=c.CustomerID AND o.PaymentState='Paid'
GROUP BY c.CustomerID
ORDER BY c.CustomerID;

Both queries return the same three rows, so the expected and actual results now agree. The corrected query preserves each customer while restricting which orders contribute to the total. It does not mean every filter belongs in ON. Predicate placement follows the desired row-preservation contract. A different requirement, such as listing only customers with paid orders, would justify a different query.

Add boundary cases when an answer needs to distinguish them. Multiple paid orders, a nullable amount, or a requested count can introduce additional rules. Explain those rules before expanding the sample. A helpful minimal example is small enough to inspect and complete enough to prevent an answer that solves the wrong problem.

Add Version and Settings to the Reproducible Script

Features and optimizer behavior depend on engine version, compatibility level, and relevant database settings. Provide those facts as text with the question. Avoid including unrelated server inventory or sensitive connection details.

SELECT @@VERSION AS VersionText;
SELECT name,compatibility_level,collation_name,
       is_read_committed_snapshot_on
FROM sys.databases WHERE database_id=DB_ID();

For a basic logical question, the version can be enough context. For a plan problem, provide the settings that influence the demonstrated behavior. State whether the issue occurs with literals, parameters, a stored procedure, or a particular transaction isolation level. That context allows another reader to choose the right test rather than guess at an execution environment.

Scrub Data Without Erasing the Problem

Replace real names, identifiers, amounts, and free text with synthetic values. Preserve the properties responsible for the failure, such as string length, Unicode characters, duplicate frequency, or date boundaries. Removing all NULLs from a NULL-related problem protects nothing useful and destroys the evidence needed to answer it.

Review comments, error messages, object definitions, file paths, and plans for secrets or private information. A sanitized table name does not sanitize a password embedded elsewhere in the script. I run the scrubbed example in a fresh test session to ensure it remains complete after that review. A helpful question should not require access to the author's private database.

Verify the Reproducible Script Before Sharing

Run the sample from start to finish in a disposable context. Check that it needs no hidden object, produces the stated actual result, and demonstrates the expected contract. Provide cleanup where persistent objects are necessary, and label any setup permissions. Temporary tables can simply end with the session when that matches the example.

Which single input row proves the distinction your question asks about? Keep that row visible in the explanation. A reproducible script lowers the effort required for a careful answer and makes competing solutions easier to evaluate. It also gives the author a concrete way to verify the answer before applying it elsewhere.

Related reading on this blog: Generating Test Data That Behaves Like the Real Thing and Test Data That Looks Like Production.

A runnable example is not extra paperwork, it is the evidence another reader needs to solve the right problem.

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