SQL SERVER – Random Number Generator Script – SQL Query

A random number generator in SQL Server needs clear rules for its range, repeatability, and use. My original range example used ROUND and the wrong multiplier. Readers correctly pointed out the uneven distribution. The examples below fix that calculation and separate the different jobs.

Generate one integer from 1 through 999

For an integer range, both endpoints should be possible. Use FLOOR with the number of integers in the range. Run this complete example in SSMS:

DECLARE @Lower int = 1;
DECLARE @Upper int = 999;
IF @Lower > @Upper
    THROW 50000, 'Lower must not exceed Upper.', 1;
DECLARE @Width bigint = CONVERT(bigint, @Upper)
                      - CONVERT(bigint, @Lower) + 1;
SELECT CONVERT(int, CONVERT(bigint, @Lower)
       + CONVERT(bigint, FLOOR(RAND() * @Width))) AS RandomInteger;

The result is an integer from 1 through 999, inclusive. Change @Lower and @Upper to your required int bounds. Negative bounds work too. Equal bounds return that one value.

The width calculation uses bigint before subtraction. That prevents overflow when the bounds span more than an int can hold. The final result still fits the declared int range. Reversed bounds raise a clear error.

These examples use SQL Server 2012 or later syntax and were tested on SQL Server 2025. They use table variables or scalar expressions, so no sample database is required.

Why the original formula missed the upper bound

The old expression multiplied RAND() by @Upper - @Lower - 1, then rounded. With bounds 1 and 999, it cannot return 999. Rounding also gives the endpoint results narrower input intervals.

The corrected expression divides the input range into equal-width intervals using FLOOR. The + 1 includes the upper integer. This removes the mapping error; it does not make RAND() a cryptographic generator.

This check uses fixed inputs and the smaller range 1 through 6. These are chosen test values, not random draws. They make the missing upper endpoint easy to reproduce:

SELECT v.SampleValue,
       CONVERT(int, ROUND((6 - 1 - 1) * v.SampleValue + 1, 0))
           AS OriginalFormula,
       1 + CONVERT(int, FLOOR((6 - 1 + 1) * v.SampleValue))
           AS CorrectedFormula
FROM (VALUES (0.000000), (0.250000), (0.500000), (0.999999))
     AS v(SampleValue)
ORDER BY v.SampleValue;

Generate a random float

RAND() produces a pseudo-random float greater than or equal to 0 and less than 1. Multiply and add to map that value to another interval:

SELECT RAND() AS RandomFloat;
SELECT 10.0 + (30.0 - 10.0) * RAND() AS RandomFloat10To30;

The second query uses the interval from 10 up to 30, excluding 30. Floating-point arithmetic is approximate. For integer endpoints, use the integer formula above instead of rounding a float.

Use a seed when a test must repeat

A fixed seed is useful when replaying a test. Calling RAND with that seed restarts its sequence for the connection. Later unseeded calls follow that seeded sequence.

The next script repeats the same two calls in the same order. Both result rows should show Yes. The displayed numbers are rounded for readability, but the equality check uses the original values.

DECLARE @Seeded1 float = RAND(42);
DECLARE @Next1 float = RAND();
DECLARE @Seeded2 float = RAND(42);
DECLARE @Next2 float = RAND();
SELECT v.Step,
       CONVERT(decimal(16,12), v.FirstRun) AS FirstRun,
       CONVERT(decimal(16,12), v.Replay) AS Replay,
       CASE WHEN v.FirstRun = v.Replay THEN 'Yes' ELSE 'No' END AS SameValue
FROM (VALUES (1, 'Seeded call', @Seeded1, @Seeded2),
             (2, 'Next call', @Next1, @Next2))
     AS v(SortOrder, Step, FirstRun, Replay)
ORDER BY v.SortOrder;

Do not call RAND(42) repeatedly when you expect a new sequence value. Each call resets the generator. Seed once, then use RAND() for subsequent values.

I also removed the old clock-based seed expression. Rapid calls can reuse the same clock value and produce repeats. Adding pieces of GETDATE() does not guarantee a fresh seed.

Generate a value for each row

Calling RAND() in a query does not promise a separate draw for every row. In this SELECT, its value repeats across the six rows. Microsoft also documents the same-value behavior for affected rows in INSERT and UPDATE statements.

For ordinary sample data, this shortcut uses NEWID() and CHECKSUM for a varying value per row. The result is between 1 and 1000:

SELECT v.RowID,
       CONVERT(decimal(10,6), RAND()) AS RandInThisSelect,
       1 + ABS(CONVERT(bigint, CHECKSUM(NEWID()))) % 1000 AS PerRowInteger
FROM (VALUES (1), (2), (3), (4), (5), (6)) AS v(RowID)
ORDER BY v.RowID;

The conversion to bigint belongs inside ABS. CHECKSUM returns an int, and ABS(-2147483648) overflows that type. Converting the result after ABS would be too late.

I use this shortcut for disposable sample data. It does not guarantee an unbiased distribution or unique values. Hashing and taking a remainder are unsuitable when exact statistical fairness matters.

Generate exactly 10,000 sample rows

For a small demonstration, a loop makes one RAND() call per inserted row. This example generates integers from 1 through 6. The counts add up to exactly 10,000:

DECLARE @Samples TABLE (RandomInteger int);
DECLARE @i int = 0;
WHILE @i < 10000
BEGIN
    INSERT INTO @Samples (RandomInteger)
    VALUES (1 + CONVERT(int, FLOOR(6 * RAND())));
    SET @i += 1;
END;
SELECT RandomInteger, COUNT(*) AS Occurrences
FROM @Samples
GROUP BY RandomInteger
ORDER BY RandomInteger;

The original loop used <= 10000 while starting at zero, which produced 10,001 rows. This version uses < 10000. It also stops reseeding from the clock on every iteration.

The category counts will vary. Equal counts are not required, and one frequency table cannot prove randomness. For large test datasets, choose a generation method designed for that workload.

Random values can repeat

A valid random number generator can produce the same value more than once. None of these bounded-number examples guarantees uniqueness. Use an IDENTITY column or a SEQUENCE when you need ordinary generated identifiers. Enforce required uniqueness with a primary key or unique constraint.

For cryptographic random bytes, SQL Server provides CRYPT_GEN_RANDOM. This example returns 16 bytes:

SELECT CRYPT_GEN_RANDOM(16) AS RandomBytes;

Do not use RAND() or the checksum shortcut for passwords or access tokens. A token design also needs suitable encoding, storage, expiration, and verification. A random-looking integer alone does not provide those properties.

Original video

The original SQL in Sixty Seconds video remains below. Use the updated scripts above as the reference for range boundaries and seeding.

More videos are available on my SQL in Sixty Seconds channel.

Check the range, repetition, and purpose before choosing a random-number expression.

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