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: MSDN
Repetitive 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
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:MSDN
If 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:
WITH RandomNumbers (RowNumber, RandomNumber) AS (
SELECT 1 AS RowNumber,
RAND( CHECKSUM( NEWID())) AS RandomNumber
UNION ALL
SELECT rn.RowNumber + 1 AS RowNumber,
RAND( CHECKSUM( NEWID())) AS RandomNumber
FROM RandomNumbers rn
WHERE rn.RowNumber < 10
)
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
WITH RandomNumbers (RowNumber, RandomNumber) AS (
SELECT 1 AS RowNumber,
RAND( CHECKSUM( NEWID())) AS RandomNumber
UNION ALL
SELECT rn.RowNumber + 1 AS RowNumber,
RAND( CHECKSUM( NEWID())) AS RandomNumber
FROM RandomNumbers rn
WHERE rn.RowNumber < 10
)
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.
