Building Demo Data With GENERATE_SERIES

The rows in a demonstration should expose the behavior you want to explain. GENERATE_SERIES makes demo data easier to create, but row count alone does not make a test realistic.

Set the Version and Compatibility Level

GENERATE_SERIES is available in SQL Server 2022 and later and requires database compatibility level 160 or higher. Verify the engine and database settings before using it. A newer engine can host a database that still uses an earlier compatibility level.

SELECT SERVERPROPERTY('ProductMajorVersion') AS ProductMajorVersion;
SELECT name,compatibility_level
FROM sys.databases WHERE database_id=DB_ID();
SELECT value FROM GENERATE_SERIES(1,5,1);

The start, stop, and step describe the requested series. Use compatible numeric types and an explicit positive step for an ascending range. For a large generator, review storage, log, and execution capacity in the disposable destination first. Do not place a million-row demonstration into a production database simply because the generating statement is short.

I define the behavior the sample must expose before generating the rows. A skewed customer distribution is useful for selectivity and plan demonstrations, while evenly spread dates can support range predicates. Write those design choices down so the example's shape is intentional rather than an unexplained accident of arithmetic.

Create a Small Customer Vocabulary for Demo Data

A small set of invented names can be reused to form a larger customer population without copying private data. Keep identifiers distinct even when names repeat. The following lab uses temporary tables and a fixed name list.

CREATE TABLE #NameParts(PartID int PRIMARY KEY,NamePart nvarchar(20) NOT NULL);
INSERT #NameParts VALUES(1,N'North'),(2,N'South'),(3,N'East'),
                      (4,N'West'),(5,N'Central');
CREATE TABLE #Customers
(
    CustomerID int PRIMARY KEY,
    CustomerName nvarchar(60) NOT NULL
);
INSERT #Customers(CustomerID,CustomerName)
SELECT CONVERT(int,g.value),
       CONCAT(n.NamePart,N' Customer ',CONVERT(nvarchar(12),g.value))
FROM GENERATE_SERIES(1,50000,1) AS g
JOIN #NameParts AS n ON n.PartID=1+(g.value-1)%5;

Repeated name components are acceptable synthetic inputs, but they do not automatically represent production name selectivity. If the demonstration investigates searches, collation, or Unicode, add the relevant controlled cases separately. Avoid pretending that a convenient generated vocabulary captures every real customer-data property.

The identifier range is a chosen test input. Validate that the generated orders reference that population. A large set of orphaned customer keys changes join behavior and can invalidate the intended experiment. Referential shape matters even when the sample is disposable and its tables have no foreign-key constraint.

Generate a Million Orders With Deliberate Skew

The following insert requests one million synthetic orders. Most generated rows point to customer 1, while the remaining rows spread across other customers. Dates span a six-year input range, categories have explicit weights, and amounts use random bytes to vary values.

CREATE TABLE #Orders
(
    OrderID bigint PRIMARY KEY,
    CustomerID int NOT NULL,
    OrderDate date NOT NULL,
    CategoryCode varchar(10) NOT NULL,
    Amount decimal(10,2) NOT NULL
);
INSERT #Orders(OrderID,CustomerID,OrderDate,CategoryCode,Amount)
SELECT CONVERT(bigint,value),
       CASE WHEN value%10<8 THEN 1 ELSE 2+CONVERT(int,value%49999) END,
       DATEADD(day,CONVERT(int,value%2192),CONVERT(date,'20200101')),
       CASE WHEN value%100<70 THEN 'Standard'
            WHEN value%100<95 THEN 'Priority' ELSE 'Special' END,
       CONVERT(decimal(10,2),(CONVERT(bigint,CRYPT_GEN_RANDOM(4))%100000)/100.0)
FROM GENERATE_SERIES(CONVERT(bigint,1),CONVERT(bigint,1000000),CONVERT(bigint,1));
CREATE INDEX IX_Orders_CustomerDate
ON #Orders(CustomerID,OrderDate) INCLUDE(Amount,CategoryCode);

The modulo expressions provide controlled weighting rather than a complete model of business behavior. They also introduce correlations between generated attributes. Those correlations must be accepted or redesigned for the test. Random amounts do not erase deterministic relationships between category, customer, date, and the sequence value.

The random-byte expression is convenient for synthetic amounts. Modulo reduction is not an unbiased statistical sampling method for every purpose. It is sufficient here to vary a bounded test amount, not to model a financial population or validate a random-number algorithm. No generated amount should be presented as a measured business value.

Validate the Demo Data Distribution You Created

Count the rows and inspect category, date, and customer distributions. Verify the customer relationship separately. These checks describe the actual generated sample; the article does not invent measured outputs for them.

SELECT COUNT_BIG(*) AS OrderCount,MIN(OrderDate) AS FirstDate,
       MAX(OrderDate) AS LastDate FROM #Orders;
SELECT CategoryCode,COUNT_BIG(*) AS CategoryCount
FROM #Orders GROUP BY CategoryCode;
SELECT TOP (10) CustomerID,COUNT_BIG(*) AS CustomerOrderCount
FROM #Orders GROUP BY CustomerID
ORDER BY CustomerOrderCount DESC,CustomerID;
SELECT COUNT_BIG(*) AS MissingCustomers
FROM #Orders AS o
WHERE NOT EXISTS(SELECT 1 FROM #Customers AS c WHERE c.CustomerID=o.CustomerID);

Include NULLs, duplicate business attributes, ties, and unusual values only when they belong to the scenario. A perfectly clean dataset is useful for some demonstrations and misleading for others. State what the sample excludes so its limitations remain visible. The generator cannot infer which inconvenient case your test is trying to catch.

Which production property changes the query's selectivity most? Match that property deliberately without importing private rows. I compare the generated shape with approved aggregate knowledge of the workload, then adjust the sample design. A million uniformly distributed rows can teach the wrong lesson about a highly concentrated workload.

Compare Hot and Cold Customer Queries

Use the supporting index and capture actual plans, reads, and CPU for different customer selectivities. The examples use recompilation to make the parameter-specific comparison clear rather than turning it into a separate plan-cache experiment.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
DECLARE @CustomerID int=1;
SELECT OrderID,OrderDate,Amount
FROM #Orders WHERE CustomerID=@CustomerID
OPTION(RECOMPILE);
SET @CustomerID=2;
SELECT OrderID,OrderDate,Amount
FROM #Orders WHERE CustomerID=@CustomerID
OPTION(RECOMPILE);
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

Fetching the large result can add client-consumption time, so distinguish server work from presentation cost. For an aggregate-focused demonstration, use an appropriate aggregate in both queries instead. Keep the result contract consistent between comparisons. Do not attribute every elapsed-time difference to the optimizer when one query returns far more data.

Preserve Repeatability for the Test You Need

Random amounts change when the data is regenerated. Keep one generated snapshot for comparisons that require identical inputs, or replace randomness with a documented deterministic expression. Recreating a random sample before every timing run introduces another uncontrolled variable. Preserve the schema, generator, chosen inputs, and validation result together.

Save the observation method with the data as well. State whether the client fetched every result row, whether a query aggregated the data, and which indexes and statistics existed during the run. Two demonstrations using the same generator can still test different workloads. Retain the exact statements and execution order to prevent an accidental comparison between unlike operations.

For a parameter-sensitivity demonstration, remove recompilation only as part of a separate deliberate experiment and document execution order. For a statistics or indexing test, retain the same sample while changing the intended variable. Repeatability is about controlling the experiment, not forcing every kind of demonstration to use one generator.

Retire the Demo Data After the Demonstration

Temporary tables disappear with the session. Persistent lab objects need a clearly reviewed cleanup sequence and a destination that cannot be confused with production. Keep the sample only when another comparison requires it, and record its generation rules with the retained copy.

Demo data is useful when its structure makes the intended behavior observable. Generate enough rows, but also preserve the relevant skew, boundary conditions, and query contract. Useful demo data becomes a good demonstration through design and validation rather than through size alone.

Related reading on this blog: Building Your Own Sample Data Set and Removing Repeated Characters From a String With GENERATE_SERIES.

A generated dataset is not a workload model by itself, it is an input whose distribution must match the behavior being tested.

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