Generating Random Numbers Per Row: RAND, NEWID and CRYPT_GEN_RANDOM

A SELECT that uses RAND() can return the same value for every row, surprising anyone creating test data. For random numbers per row, NEWID and CRYPT_GEN_RANDOM provide per-row inputs with different costs and guarantees. Choose the method for test data, repeatable tests, or security-sensitive values.

Reproduce the RAND Surprise

RAND without a varying seed is evaluated as one value for the statement in this common pattern. Multiplying it by ten does not make a new draw for each output row. A seed makes the value repeatable for a run. Still, RAND(42) in each selected row is still not a per-row sequence.

SELECT TOP (10)
       ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_id,
       RAND() AS rand_value,
       RAND(42) AS seeded_value
FROM sys.all_objects;

I use this tiny output before generating a million test rows. It catches the difference between a function call written on each SELECT row and random numbers actually produced per row. What property matters for the task: unpredictability, repeatability, approximate spread, or speed?

Get Random Numbers Per Row From NEWID

NEWID produces a new GUID per row in a query, and CHECKSUM maps it to an integer. The familiar expression ABS(CHECKSUM(NEWID())) % n creates buckets from 0 to n-1. It has two caveats. ABS can overflow for the minimum int value, and modulo introduces slight distribution bias. For casual test data, a bigint cast avoids the overflow.

DECLARE @n int = 10;
SELECT TOP (20)
       ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_id,
       ABS(CONVERT(bigint, CHECKSUM(NEWID()))) % @n AS bucket
FROM sys.all_objects;

This is not a cryptographic random number generator. CHECKSUM compresses a GUID into 32 bits and can collide. If the value protects access, selects a secret, or must resist prediction, use a security-reviewed design instead. For synthetic test buckets, record the method and the seed policy in the test data script.

Use CRYPT_GEN_RANDOM for Stronger Random Numbers Per Row

CRYPT_GEN_RANDOM returns random bytes and is the stronger source when unpredictability matters. Convert four bytes to an integer, clear the sign bit, then reduce to a bucket for a simple demonstration. Modulo still has bias when the range is not a divisor of the input space. This exact expression is therefore not a complete secure uniform sampler.

DECLARE @n int = 10;
SELECT TOP (20)
       ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_id,
       (CONVERT(int, CRYPT_GEN_RANDOM(4)) & 2147483647) % @n
         AS bucket
FROM sys.all_objects;

The function costs more than a simple checksum and changes each run. Do not use it to make a report reproducible. For tokens or security decisions, follow the application's cryptographic library and use rejection sampling for an unbiased bounded range. The SQL expression here shows per-row generation, not a complete authentication design.

Make a Repeatable Sequence With Seeded RAND

A seed gives RAND a reproducible sequence when successive calls occur in a controlled loop or session. It is useful for a test harness that must reproduce values. Do not assume SELECT row order or parallel evaluation will preserve a reproducible per-row assignment in a set query. Generate and store the values in a stable key order instead.

DECLARE @i int = 1, @value float;
SET @value = RAND(42);
WHILE @i <= 10
BEGIN
    SET @value = RAND();
    SELECT @i AS row_id, @value AS seeded_sequence_value;
    SET @i += 1;
END;

Run the batch twice in a clean test session and compare values. RAND's state is session-related, so seed deliberately at the start of the sequence. This loop is for a small reproducible sample, not a recommendation to insert a million rows one at a time.

Check How Random Numbers Per Row Spread Across Buckets

A quick GROUP BY shows whether a generator assigns rows across the intended bucket range. It cannot prove cryptographic quality or perfect uniformity. Generate enough rows that random variation is visible but not mistaken for a bug. Compare counts and min/max bucket values for NEWID and CRYPT_GEN_RANDOM on the same row count.

WITH draws AS
(
    SELECT TOP (100000)
           ABS(CONVERT(bigint, CHECKSUM(NEWID()))) % 10 AS bucket
    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
)
SELECT bucket, COUNT_BIG(*) AS rows_in_bucket
FROM draws GROUP BY bucket ORDER BY bucket;

Repeat the query for the cryptographic expression, and expect the counts to differ between runs. A ratio near ten percent for each bucket is a smoke test, not a statistical certification. If a skewed result would affect business decisions, use an appropriate sampling method and formal validation.

Pick One Random Row Without Sorting All Rows

ORDER BY NEWID() assigns a GUID to every row and sorts the full input, which is expensive on a large table. If a stable indexed key exists, count rows, choose a random offset, and seek through ordered rows with OFFSET/FETCH. This avoids a random sort but can still scan many index entries to reach a late offset. Measure it on the real table.

DECLARE @count bigint = (SELECT COUNT_BIG(*) FROM dbo.Customers);
IF @count > 0
BEGIN
    DECLARE @offset bigint =
        ABS(CONVERT(bigint, CHECKSUM(NEWID()))) % @count;
    SELECT CustomerID
    FROM dbo.Customers
    ORDER BY CustomerID
    OFFSET @offset ROWS FETCH NEXT 1 ROW ONLY;
END;

A dense numeric key can support a random-key seek with a retry for gaps. Do not assume IDs are contiguous. For very large tables, consider a maintained sampling key or a deliberately approximate method. The right answer depends on whether every row must have equal chance and how much I/O the selection can spend.

Treat Modulo as a Mapping, Not a Generator

The % @n operation only maps an integer into a smaller range. It does not improve the randomness of the input. If the source has uneven low bits or a limited domain, buckets inherit that weakness. A quick GROUP BY can reveal a glaring problem, but a convincing statistical review needs repeated samples and clear acceptance criteria. For ordinary test data, approximate spread is enough; for lotteries or security decisions, it is not.

Keep Test Runs Reproducible

When a test fails on random data, save the generated rows or the seed and algorithm version. Replaying only RAND(42) is insufficient if code changes the number or order of calls. A stable table keyed by test case is more reliable than a query that depends on optimizer row order. I keep a small fixed fixture for regression tests and use random generation to discover additional cases. The found case is then preserved as a deterministic test.

Validate the Sampling Contract

Choosing a random row with OFFSET gives each position equal chance only when the count and ordered set stay stable during selection. Concurrent inserts and deletes can change positions between the count and fetch. Use snapshot isolation or a stable captured key set when strict fairness matters. On a very large table, a late offset can still read many index entries. Benchmark it against alternative indexed sampling designs rather than assuming no sort means no cost.

Related reading on this blog: Techniques for Retrieving Random Rows and Selecting Random n Rows from a Table.

A random function is not interchangeable with another, it is a choice based on repeatability and scope.

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