Generating Test Data That Behaves Like the Real Thing

Useful test data generation reproduces the properties that affect your query, not just a large row count. Skew, relationships, and row width can change the plan long before the table becomes enormous.

Describe the Shape Before Generating Rows

List the properties your experiment needs to preserve. A sales table might have a few large customers, many small customers, and most activity in recent dates. Uniform random values can erase all three patterns.

Also consider NULL frequency, duplicate business keys, string length, and relationships between columns. Independent random values can create combinations that never occur in real data. The optimizer's estimates depend on more than the total number of rows.

Use synthetic values when production data is confidential. Preserving a useful distribution does not require preserving people's identities. Document which properties were copied conceptually and which were simplified.

Generate a Repeatable Sequence

A deterministic number sequence makes the dataset easy to rebuild. Use it to derive keys and controlled distributions. Keep generation separate from the timed workload so setup cost does not contaminate query measurements.

CREATE TABLE #TestOrders
(
    OrderId int PRIMARY KEY,
    CustomerId int NOT NULL,
    OrderDate date NOT NULL,
    Status varchar(10) NOT NULL,
    CommentText varchar(300) NULL
);
WITH Digits AS
(
    SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS d(n)
), Numbers AS
(
    SELECT a.n + 10*b.n + 100*c.n + 1000*d.n AS n
    FROM Digits AS a CROSS JOIN Digits AS b
    CROSS JOIN Digits AS c CROSS JOIN Digits AS d
)
INSERT #TestOrders
SELECT n,
       CASE WHEN n < 8000 THEN 1 ELSE 2 + n % 200 END,
       DATEADD(day, n % 60, CONVERT(date, '20260101', 112)),
       CASE WHEN n % 10 = 0 THEN 'Open' ELSE 'Closed' END,
       CASE WHEN n % 7 = 0 THEN NULL ELSE REPLICATE('x', 20 + n % 250) END
FROM Numbers;

These counts and proportions are inputs to the example, not observed production measurements. Change them to match your test question. The example intentionally creates a much more common customer value.

Verify the Distribution You Intended

Do not assume the generator produced the desired shape because the INSERT completed. Group by important keys and inspect ranges and missing values. A small arithmetic mistake can turn a skewed test into a uniform one.

SELECT CustomerId, COUNT_BIG(*) AS order_count
FROM #TestOrders
GROUP BY CustomerId
ORDER BY order_count DESC;
SELECT COUNT_BIG(*) AS total_rows,
       SUM(CASE WHEN CommentText IS NULL THEN 1 ELSE 0 END) AS missing_comments,
       MIN(OrderDate) AS first_date, MAX(OrderDate) AS last_date,
       AVG(CONVERT(decimal(12,2), DATALENGTH(CommentText))) AS average_nonnull_comment_bytes
FROM #TestOrders;

The average excludes NULL values because AVG ignores them. Report missing-value frequency separately rather than hiding it inside one average. Keep the verification query with the generator so changes remain reviewable.

Test Common and Uncommon Inputs

A plan that works for a rare key may behave differently for a popular key. Test both through the same parameterized interface. Inspect estimates, actual rows, and access paths before drawing a conclusion.

CREATE INDEX IX_TestOrders_Customer ON #TestOrders(CustomerId);
EXEC sys.sp_executesql
 N'SELECT OrderId, CustomerId, OrderDate, CommentText
   FROM #TestOrders WHERE CustomerId = @CustomerId;',
 N'@CustomerId int', @CustomerId = 1;
EXEC sys.sp_executesql
 N'SELECT OrderId, CustomerId, OrderDate, CommentText
   FROM #TestOrders WHERE CustomerId = @CustomerId;',
 N'@CustomerId int', @CustomerId = 199;

Run with actual plans enabled when studying optimizer behavior. The chosen plan depends on version, compatibility, statistics, and compilation context. Do not attach invented timings or assume this sample guarantees a particular plan.

Add Relationships and Awkward Cases

For join tests, create related parent and child distributions rather than two independent random tables. Preserve fan-out where it matters. One customer with many orders can dominate a join even when most customers have very few.

Add boundary dates, missing lookups, and duplicate candidates when testing correctness. Keep invalid cases separate when measuring a valid production workload. Otherwise, an error path can become the accidental subject of the benchmark.

Include realistic row widths and payload access. A narrow key-only query may fit an index that the application's wider query cannot use alone. Repeated identical strings can also distort compression assumptions.

Record What the Dataset Cannot Prove

A static dataset does not reproduce concurrent writers, transaction length, network latency, or storage contention. Add those dimensions deliberately when they matter. More rows cannot substitute for a missing workload characteristic.

Save the generator, schema, indexes, settings, and your actual measurements together. State the assumptions that make the test representative. Realistic testing begins when the data's limitations are as clear as its size.

Realistic test data is not just more rows, it is the right imbalance and relationships.

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