Repeatable Test Data With a Fixed Random Seed

Reproducing a failed test starts with reproducing its input. Repeatable test data lets another developer examine the same names, amounts, and boundary conditions.

Decide What Repeatable Test Data Must Repeat

A reproducible fixture needs stable values as well as a known row count. Record the generator version, seed, and input range. Those details explain what another person must recreate when investigating a failure.

I prefer synthetic names over copied customer details in test fixtures. I also keep explicit edge cases beside generated rows. Random-looking data does not guarantee that important business boundaries appear.

Two useful patterns are a seeded sequence and a deterministic value per row. The first depends on call order. The second derives each value independently from its row identifier and a stable input string.

Neither pattern is a security mechanism. Predictable generated values are useful precisely because they can be reproduced. Do not use these fixtures for authentication tokens, encryption material, or other security-sensitive values.

Seed RAND Once Per Sequence

RAND accepts a seed and influences subsequent calls on that connection. Initialize it once, then call RAND separately for each required value. Resetting the same seed for every row repeats the first value instead.

Do not assume an INSERT SELECT evaluates RAND independently for every row. SQL Server can give affected rows the same value in that usage. The small loop below makes each call explicit.

The script creates two passes in one temporary table. Each pass initializes the same seed and follows the same call order. Generated names are synthetic labels, and generated amounts are bounded fixture values.

CREATE TABLE #SeedFixture
(
    PassNumber int NOT NULL,
    RowNumber int NOT NULL,
    SampleName varchar(30) NOT NULL,
    Amount decimal(12,2) NOT NULL,
    PRIMARY KEY (PassNumber, RowNumber)
);
DECLARE @Pass int = 1, @Row int, @Discard float;
DECLARE @NameNumber int, @Cents int;
WHILE @Pass <= 2
BEGIN
    SET @Discard = RAND(417);
    SET @Row = 1;
    WHILE @Row <= 100
    BEGIN
        SET @NameNumber = CONVERT(int, FLOOR(RAND() * 100000));
        SET @Cents = CONVERT(int, FLOOR(RAND() * 100000));
        INSERT #SeedFixture
        VALUES (@Pass, @Row,
                'Sample-' + CONVERT(varchar(10), @NameNumber),
                CONVERT(decimal(12,2), @Cents / 100.0));
        SET @Row += 1;
    END;
    SET @Pass += 1;
END;
SELECT RowNumber, SampleName, Amount
FROM #SeedFixture WHERE PassNumber = 1 ORDER BY RowNumber;

Preserve the Sequence Contract

Adding a new RAND call changes the position of every later call. That can change subsequent names and amounts despite using the same seed. Version the generator whenever its call sequence changes.

Keep the example on one connection with no unrelated RAND calls between fixture calls. A helper that consumes additional values changes the sequence. Shared pooled connections also require explicit initialization for each fixture generation.

The loop intentionally favors clarity over bulk-generation speed. A hundred rows is a chosen demonstration size. Larger fixtures usually benefit from independent row-derived values rather than a long procedural loop.

Generated name labels can repeat because their numeric range is finite. RowNumber remains the fixture identity. Do not apply a unique-name requirement unless the generation method explicitly provides it.

Assert That Repeatable Test Data Matches across Passes

An ordered display is useful for inspection but does not prove equality. Compare the actual values across passes. EXCEPT catches missing or different rows without relying on their displayed order.

Use both directions of comparison when checking equal sets. A one-way comparison can miss additional rows in the other pass. The primary key separately protects one row per pass and row number.

IF EXISTS
(
    SELECT RowNumber, SampleName, Amount
    FROM #SeedFixture WHERE PassNumber = 1
    EXCEPT
    SELECT RowNumber, SampleName, Amount
    FROM #SeedFixture WHERE PassNumber = 2
)
OR EXISTS
(
    SELECT RowNumber, SampleName, Amount
    FROM #SeedFixture WHERE PassNumber = 2
    EXCEPT
    SELECT RowNumber, SampleName, Amount
    FROM #SeedFixture WHERE PassNumber = 1
)
    THROW 50001, 'Seeded fixture passes differ.', 1;

The equality follows from the identical seed and call sequence. On SQL Server 2025 the check finishes without an error, so both passes match. Rerun it after any generator change.

Also validate the intended value ranges and total membership. Equality alone can confirm that two equally broken generators match. A fixture contract should state both reproducibility and acceptable values.

Derive Repeatable Test Data from Stable Row Numbers

For set-based generation, hash a canonical string containing the seed and row number. Include a field label to distinguish names from amounts. SHA2_256 provides a stable hash for the same encoded input.

The following script builds row numbers from explicit digit sets. It converts four hash bytes to a nonnegative bigint value. Applying a remainder maps that value into the chosen fixture range.

CREATE TABLE #RowNumbers (RowNumber int NOT NULL PRIMARY KEY);
INSERT #RowNumbers(RowNumber)
SELECT T.N * 10 + U.N + 1
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS T(N)
CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS U(N);

DECLARE @Seed int = 417;
SELECT N.RowNumber,
       'Sample-' + CONVERT(varchar(10), V.NameValue % 100000) AS SampleName,
       CONVERT(decimal(12,2), (V.AmountValue % 100000) / 100.0) AS Amount
INTO #HashFixture
FROM #RowNumbers AS N
CROSS APPLY
(
    SELECT HASHBYTES('SHA2_256',
               CONVERT(nvarchar(100), CONCAT(@Seed, '|name|', N.RowNumber))) AS NameHash,
           HASHBYTES('SHA2_256',
               CONVERT(nvarchar(100), CONCAT(@Seed, '|amount|', N.RowNumber))) AS AmountHash
) AS H
CROSS APPLY
(
    SELECT CONVERT(bigint, SUBSTRING(H.NameHash, 1, 4)) AS NameValue,
           CONVERT(bigint, SUBSTRING(H.AmountHash, 1, 4)) AS AmountValue
) AS V;
SELECT RowNumber, SampleName, Amount
FROM #HashFixture ORDER BY RowNumber;

Keep Row Identity and Encoding Stable

A row-derived method does not depend on execution order. Adding row one hundred one leaves existing row inputs unchanged. That makes repeatable test data easier to extend without rewriting the entire fixture.

The exact hash input still matters. Changing separators, field labels, character encoding, or number formatting changes generated values. Keep the canonical input definition in the fixture's versioned specification.

Modulo mapping introduces a small distribution bias unless the source range divides evenly. It is acceptable for these ordinary demonstration labels. It is not a promise of a statistically perfect random distribution.

The amount range excludes its upper limit by construction. Add explicit maximum, minimum, NULL, and invalid-value cases when those behaviors matter. Uniform-looking samples alone do not test conversion and validation boundaries reliably.

Use Fresh Randomness for the Right Job

When would varying the input help more than repeating it? Exploratory stress testing can uncover relationships a fixed fixture misses. Save the generated input or a reproducible seed whenever a failure needs later investigation.

Fresh randomness and stable regression fixtures serve different purposes. Use predictable cases for behavior that must remain protected. Add varied cases for discovery, then promote significant failures into permanent reproducible tests.

I use repeatable test data to shorten the distance between a failure and its explanation. I keep the generator and assertions together. A test should not change its alibi every time it runs.

Keep personal data out of synthetic labels and document the generator limits. Review distributions when testing skew, selectivity, or capacity. Reproducibility gives you the same experiment, while a thoughtful fixture makes that experiment useful.

Use stable row identifiers from an explicit source rather than incidental scan order. ROW_NUMBER needs a unique ordering key when it creates those identifiers. An unstable ordering can assign another record the same generated amount during the next run.

For deterministic hash fixtures, select a few known row inputs as permanent sentinels. Compare their generated values after changing the generator. This detects accidental changes to encoding or field labels before a large fixture silently changes.

Record the database version and relevant fixture settings when reproducibility spans environments. The hash example explicitly converts its input to nvarchar. Avoid implicit conversion paths that depend on unspecified data types or inconsistent source representations.

Finally, keep generated data separate from expected results. Computing both with the same incorrect formula can make a broken test pass. Derive critical expected outcomes from the business rule and a small independently reviewed set of cases.

Related reading on this blog: Generating Random Numbers Per Row: RAND, NEWID and CRYPT_GEN_RANDOM and Test Data That Looks Like Production.

A fixed seed is not complete test coverage, it is a reliable way to revisit the same input.

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