The available sample has the right table names and the wrong behavior. A sample data set you build yourself can expose the join, constraint, or plan choice the lesson needs without copying private production rows.
Describe the Behavior First
Do not generate rows until you know the question the sample must answer. A lesson about duplicate joins needs a parent with several children. A lesson about skew needs a few very common values and many rare ones. A lesson about NULL needs meaningful missing values. Write the expected query result before creating the tables.
I begin with a tiny handwritten fixture that proves the rule. If the tiny version does not reveal the issue, a million random rows will not help. Which row is the edge case? Name it. The later bulk generator should preserve that shape while adding enough volume for the plan or storage behavior you want to teach.
Choose a Simple Business Story for the Sample Data Set
Use a small domain such as customers, orders, and order lines. Give each table a clear grain: one customer, one order, one line. Add primary keys and foreign keys so relationships are real. Avoid extra columns that do not support the lesson. A sample data set should be rich enough to teach, but simple enough that readers can hold it in their heads.
I document what every column means. A StatusCode without a list of allowed states invites inconsistent examples. Dates need a time-zone rule if they represent instants. A clear schema reduces the explanation burden and makes failures easier to diagnose. Do not build a miniature enterprise just to demonstrate GROUP BY.
Seed Known Edge Cases
Start with deterministic inserts. Include one customer with no orders, one order with multiple lines, and one canceled order if the lesson needs status filtering. Use explicit keys in the test fixture so assertions stay stable. The example creates two simple tables and rows that make a LEFT JOIN meaningful. Run it only in a disposable database.
For a longer-lived sample, put table creation and seed data in separate scripts and add cleanup. I prefer a script that fails on an unexpected existing table over one that silently merges old and new fixture data. That keeps a rerun honest.
CREATE TABLE dbo.SampleCustomer
(
CustomerId int NOT NULL PRIMARY KEY,
CustomerName nvarchar(100) NOT NULL
);
CREATE TABLE dbo.SampleOrder
(
OrderId int NOT NULL PRIMARY KEY,
CustomerId int NOT NULL
REFERENCES dbo.SampleCustomer(CustomerId),
Amount decimal(12,2) NOT NULL
);
INSERT dbo.SampleCustomer(CustomerId, CustomerName)
VALUES (1, N'North Shop'), (2, N'West Shop');
INSERT dbo.SampleOrder(OrderId, CustomerId, Amount)
VALUES (101, 1, 25.00), (102, 1, 40.00);
Add Realistic Distributions
Uniform random values are easy to generate and rarely resemble a business workload. Decide which customers are active, which products are popular, and how activity changes over time. Use synthetic values and published aggregate shapes, not copied personal data. Keep the generator seed or rules so two builds are comparable. If you change the distribution, version the fixture.
I inspect counts by the columns used in predicates and joins. A row count alone cannot show skew. Some queries need a high concentration of one value to produce a different estimate. Others need sparse NULLs or a long tail of small groups. Match the pattern to the lesson rather than increasing volume blindly.
Validate Sample Data Set Relationships and Expected Counts
A generator can produce orphaned child rows, accidental duplicates, or missing categories. Run checks after loading. The first query finds sample customers with no orders, which the fixture intentionally includes. The second query checks for orphaned orders, which it should not include. Convert those expectations into assertions in the build process.
I also compare distributions with the design note. If every customer gets exactly the same number of orders, a skew lesson has failed even if every foreign key is valid. What should the data make easy to observe? Treat that as an acceptance condition.
SELECT c.CustomerId
FROM dbo.SampleCustomer AS c
LEFT JOIN dbo.SampleOrder AS o ON o.CustomerId = c.CustomerId
WHERE o.OrderId IS NULL;
SELECT o.OrderId
FROM dbo.SampleOrder AS o
LEFT JOIN dbo.SampleCustomer AS c ON c.CustomerId = o.CustomerId
WHERE c.CustomerId IS NULL;Reset the Sample Data Set Predictably
A sample used in repeated demos changes after each run. Save a baseline backup, use a disposable database build, or create a reset script that restores the intended rows. Choose one reset method and test it. A manual cleanup step will eventually be forgotten at the least convenient moment. Keep the reset time short enough for a rehearsal.
I include a validation query after reset. It checks the schema version, expected keys, and distribution markers. If the reset fails, stop the lesson rather than continuing with stale data. The database should return to a known state, not merely look clean in Object Explorer.
Share the Install Guide and Fixture
When a Microsoft sample does fit part of the lesson, the existing install guide provides the AdventureWorks and WideWorldImporters setup path. For a custom fixture, provide the create, seed, validate, and reset scripts together. State the minimum SQL Server version and database compatibility level needed by the scripts.
I keep the README short and practical. It tells a reader which database to create, which script runs first, and what success looks like. A sample dataset is useful when another person can build it without a private conversation with the author. The install path is part of the lesson.
A reusable fixture needs a data contract. Document which tables are intentionally sparse, which relationships are complete and which edge cases are planted. Keep enough variation to teach joins, filtering and aggregation without making the fixture too large to reset. I include nulls, duplicates where the schema permits them, and boundary dates only when an exercise explains why they are there. Random-looking rows can make a classroom query harder to debug without making it more realistic.
Use deterministic generation or a checked-in seed script so every learner starts with the same answer. Test rerunning the seed script: either it should cleanly reset the fixture or explicitly refuse to overwrite an existing one. A sample data set is useful when the expected result can be explained. If no one can say why a row exists, it is noise. The setup guide and seed version should travel together.
Related reading on this blog: Install AdventureWorks and WideWorldImporters: Updated 2026 and SQL SERVER 2022: GENERATE_SERIES Function.
A sample data set is not a pile of random rows, it is a repeatable example of one behavior.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
