Generating a set of random numbers in SQL Server
Generate a set of random numbers by using CTE
Introduction
Sometimes generating a set of random numbers needs to be done in the database. As always this can be done in numerous ways. This tip show how to use CTE (Common Table Expression) to achieve this.
This tip is based on my previous tip, so to get to know the CTE behaviour better, please refer to Generating desired amount of rows in SQL using CTE
Random numbers, how?
The first thing is to think about the random number generation. SQL Server has RAND function but as the documentation says:
Quote: MSDNRepetitive calls of RAND() with the same seed value return the same results.
For one connection, if RAND() is called with a specified seed value, all subsequent calls of RAND() produce results based on the seeded RAND() call.
So clearly the random number needs to be generated so that the seed itself is changing between calls. For this we'll utilize both NEWID and CHECKSUM functions.
Again, according to MSDN each call to NEWID function returns a unique value of type uniqueidentifier. So this helps to generate different values. Now the problem is that the RAND function is expecting an integer for the seed and the NEWID returns wrong type of data. Because of this CHECKSUM is used to generate a "quite unique" integer value from the uniqueidentifier.
So generating a single random value the call looks like this
-------------------------------------
-- Generate a random number
-------------------------------------
SELECT RAND( CHECKSUM( NEWID()));
GO
So why is the result almost unique and not unique. The reason is the CHECKSUM function. Even though the NEWID produces unique values, the CHECKSUM may return the same value for two different inputs. Again from MSDN:
Quote:MSDNIf one of the values in the expression list changes, the checksum of the list also generally changes. However, there is a small chance that the checksum will not change.
So if you're ready to accept this flaw, keep on reading. :)
Generating the set
If you read the previous tip the rest is easy. To generate a set of random we'll use same kind of CTE as in the previous tip. Something like this:
-------------------------------------
-- Generate a set of random numbers
-- (values between 0 and 1)
-------------------------------------
WITH RandomNumbers (RowNumber, RandomNumber) AS (
-- Anchor member definition
SELECT 1 AS RowNumber,
RAND( CHECKSUM( NEWID())) AS RandomNumber
UNION ALL
-- Recursive member definition
SELECT rn.RowNumber + 1 AS RowNumber,
RAND( CHECKSUM( NEWID())) AS RandomNumber
FROM RandomNumbers rn
WHERE rn.RowNumber < 10
)
-- Statement that executes the CTE
SELECT rn.RowNumber, rn.RandomNumber
FROM RandomNumbers rn;
GO
This produces ten random numbers, like:
RowNumber RandomNumber
--------- ------------------
1 0,926450498042473
2 0,263034727608849
3 0,0442986719277323
4 0,411751210736538
5 0,502681292790595
6 0,598202260217818
7 0,130291670852493
8 0,398866254997778
9 0,0701463958741442
10 0,542893926043556
As you see the generated numbers are between zero and one. So lets say you need random numbers between 0 and 1000 the query could look like
-------------------------------------
-- Generate a set of random numbers
-- (values between 0 and 1000)
-------------------------------------
WITH RandomNumbers (RowNumber, RandomNumber) AS (
-- Anchor member definition
SELECT 1 AS RowNumber,
RAND( CHECKSUM( NEWID())) AS RandomNumber
UNION ALL
-- Recursive member definition
SELECT rn.RowNumber + 1 AS RowNumber,
RAND( CHECKSUM( NEWID())) AS RandomNumber
FROM RandomNumbers rn
WHERE rn.RowNumber < 10
)
-- Statement that executes the CTE
SELECT rn.RowNumber AS RowNumber,
ROUND(rn.RandomNumber * 1000, 0) AS RoundedRandomNumber
FROM RandomNumbers rn;
GO
And the result would be something like
RowNumber RoundedRandomNumber --------- ------------------- 1 483 2 768 3 592 4 271 5 277 6 922 7 710 8 909 9 276 10 491
If you need more than 100 rows of data, remember that the default for MAXRECURSION is 100 and it can be changed using OPTION clause. For more information, see Generating desired amount of rows in SQL using CTE.
References
The following links may be useful for further information:
History
- 26th August, 2014: Created.