Click here to Skip to main content
11,500,844 members (72,842 online)
Click here to Skip to main content

Generating a set of random numbers in SQL Server

, 26 Aug 2014 CPOL 5.8K 55 7
Rate this:
Please Sign up or sign in to vote.
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: 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

-------------------------------------
-- 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: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. Smile | :)

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Mika Wendelius
Architect
Finland Finland
I've been a programmer since mid 80's using languages like assembler, C/C++, PL/I (mainframe environment), pascal, VB (I know, I know, no comments please) and C# and utilizing different techniques and tools.

However I'm specialized in databases and database modeling. Mostly I have used products like Oracle (from version 6), SQL Server (from version 4.2), DB2 and Solid Server (nowadays an IBM product).

For the past 10+ years my main concerns have been dealing with different business processes and how to create software to implement and improve them. At my spare time (what ever that actually means) I'm also teaching and consulting on different areas of database management, development and database oriented software design.

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150520.1 | Last Updated 26 Aug 2014
Article Copyright 2014 by Mika Wendelius
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid