Click here to Skip to main content
15,868,016 members
Articles / Database Development / SQL Server
Tip/Trick

Generating a set of random numbers in SQL Server

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
26 Aug 2014CPOL2 min read 53.6K   191   7   2
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

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

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:

SQL
-------------------------------------
-- 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

SQL
-------------------------------------
-- 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)


Written By
Architect
Europe Europe
Biography provided

Comments and Discussions

 
GeneralUnfortunately, because it's a recursive query Pin
Derek Broughton15-Mar-17 5:54
Derek Broughton15-Mar-17 5:54 
GeneralRe: Unfortunately, because it's a recursive query Pin
Wendelius15-Mar-17 7:01
mentorWendelius15-Mar-17 7:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.