Click here to Skip to main content
Click here to Skip to main content

Thinking in SQL - Generating Random Numbers

, 23 Jan 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
A discussion of ways to create random number sequences in SQL Server

 Introduction

Welcome back. I confess, I am considering dropping the episode numbers, as each article is becoming less sequential and more self contained. I intend to keep writing SQL articles and using the overarching title, just to group them together, and still create the concept of a series that can be worked through as if it were a book, to allow people wanting to learn to stick with one set of information and not just read random articles of varying style, quality and accuracy.

I've also decided to rename the series to 'Thinking in SQL', because SQL does require a different mindset, and that's what all my articles are trying to encourage. 

Databases are generally used to store data, in a structured format, so we can rely on them to provide us with the same information every time we make the same request. Nevertheless, sometimes we do want to generate random numbers inside SQL Server, not least when we're trying to populate test data. So, today's article is about how generating random numbers works in SQL Server. As always, it's more interesting and has more points to consider than you might first think.

Generating a random number

select rand()

That's all you need to do, to generate a random number in SQL. This creates a value between 0 and 1, so you can create a range up to xx by multiplying by xx, and you can convert to int to make it a whole number, if you require it. The conversion is also implicit if you just use it somewhere that a whole number is expected.

Generating a random number sequence

Now run this to get a set of five random numbers:

select rand() union all 
select rand() union all 
select rand() union all 
select rand() union all 
select rand() 

Pretty straightforward, right ? But, it does not scale well if you wanted to create a LOT of them. Enter the tally table. Here is SQL to use a tally table to generate 100 random numbers.

WITH 
  E1(N) AS (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
           ),                          -- 1*10^1 or 10 rows
  E2(N) AS (SELECT 1 FROM E1 a, E1 b) -- 1*10^2 or 100 rows
	select rand() from e2 
;

What happened ? SQL Server has optimised our query and decided to call rand() once. It doesn't realise that it returns a different value every time, and so assumes that it's being efficient for us. This is, in general, a good thing, but in this case, it's an issue.

The first way we'll try to abstract out the random number generation, is with a view. Create this:

CREATE VIEW vwRand
AS
SELECT RAND() as Rand

go 

And then run it like this: 

WITH 
  E1(N) AS (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
           ),                          -- 1*10^1 or 10 rows
  E2(N) AS (SELECT 1 FROM E1 a, E1 b) -- 1*10^2 or 100 rows
	select rand() from e2, vwRand 
;
This is a cross join, but because vwRand has one value in it, we still get 100 values back. Sadly, they are all the same. So, what can we run in SQL that knows not to be optimised away ? The answer is, a user defined function.
CREATE FUNCTION RandNumber()
RETURNS float
AS
  BEGIN
  RETURN (SELECT rand() as  RandNumber)
  END

go 
This is not allowed. Rand is considered a 'side effecting function' ( perhaps it increments a sequence, all 'random' sequences being not really random at all ). So, we need to do it in two steps, as follows:
-- Function to create random number by selecting from view
CREATE FUNCTION Random()
RETURNS float
AS
  BEGIN
  RETURN (SELECT Rand FROM vwRand)
  END

go 
So, our view is still creating the random number, but a function is causing the view to be invoked for every row. This is a good lesson in terms of the efficiency of functions in general, but in our case, it's perfect.
WITH 
  E1(N) AS (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
           ),                          -- 1*10^1 or 10 rows
  E2(N) AS (SELECT 1 FROM E1 a, E1 b) -- 1*10^2 or 100 rows
	select dbo.random() from e2
;
This finally gives us 100 random numbers. Of course, they are all between 0 and 1. So long as we have this wrapped up at all, may as well make it easy to specify what our range is. Here is a function to do this:
CREATE FUNCTION RandomNumber(@max int)
RETURNS float
AS
  BEGIN
  RETURN (SELECT Rand * @max FROM vwRand)
  END

go 
And now you can call this to get a sequence of random values up to the specified maximum
WITH 
  E1(N) AS (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
           ),                          -- 1*10^1 or 10 rows
  E2(N) AS (SELECT 1 FROM E1 a, E1 b) -- 1*10^2 or 100 rows
	select dbo.randomNumber(40) from e2
;
go
If we wanted to get random values that are ints, we could cast to int, or we can take a different approach. This code takes the random number, moves the decimal place across, then uses the % operator ( which is mod ) to strip down to the remainder from the value. As this will return a range of 0 to n-1, we add one to the result.
CREATE FUNCTION RandomInt(@max int)
RETURNS int
AS
  BEGIN
  RETURN (SELECT convert(int, Rand * 100000)% @max FROM vwRand) + 1
  END
go 
And here's the code to call it.
WITH 
  E1(N) AS (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
           ),                          -- 1*10^1 or 10 rows
  E2(N) AS (SELECT 1 FROM E1 a, E1 b) -- 1*10^2 or 100 rows
	select dbo.randomInt(50) from e2
;

Benchmarking our results

The big question with any random sequence is, how random is it ? We're going to use SQL to try to find out. Our strategy will be to create a small range of numbers, count them and see if the spread is reasonably even. If we asked for a value between 0 and 5, and it was 4 60% of the time, that would not indicate a reasonable spread of values...
WITH 
  E1(N) AS (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
           ),                          -- 1*10^1 or 10 rows
  E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows
  rand as(select dbo.randomInt(5) as r from e2)
  select r, count(r) from rand group by r order by count(r) desc
;
This will return a result along the lines of: 
2	26
3	21
1	19
4	18
5	16
Which is a reasonable sort of spread ( if they were all 20, that would not be random, either ).

Another way to create random numbers

Another way to create random numbers, is to start with a GUID. A GUID is, by definition, random. The following code casts a GUID to a binary value of 6 bytes. We then use the mod operator to clamp down to the range we want, maximum possible values exceed 2 billion. This also converts the value to an int, but it could be negative, so we use ABS to strip the negative sign, and again add one to clamp to our range (as above)
SELECT ABS(CAST(NEWID() AS binary(6)) %10) + 1 
The next question is, will this work in inline code ? Run this and you will see it does work, NewId() is called for every row.
WITH 
  E1(N) AS (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
           ),                          -- 1*10^1 or 10 rows
  E2(N) AS (SELECT 1 FROM E1 a, E1 b) -- 1*10^2 or 100 rows
	select ABS(CAST(NEWID() AS binary(6)) %10) + 1  from e2 
;
However, it's enough of a mouthful to make sense to wrap it in a view and a function. We need a function in order to be able to pass in the maximum value we want our range to be in, otherwise we could just use a view. A function makes more sense though, as we are after a single value.  It is worth mentioning that using functions will slow down your SQL, so you should make an intelligent choice based on your needs.
create VIEW vwRandFromId
AS
	select abs(convert(int, CAST(NEWID() AS binary(6)))) as rand
go 

CREATE FUNCTION RandomIntFromId(@max int)
RETURNS int
AS
  BEGIN
  RETURN (SELECT Rand% @max FROM vwRandFromId)
  END
Now we can do this where ever we need to:
WITH 
  E1(N) AS (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
           ),                          -- 1*10^1 or 10 rows
  E2(N) AS (SELECT 1 FROM E1 a, E1 b) -- 1*10^2 or 100 rows
	select dbo.RandomIntFromId(500) from e2 
;
Once again, let's see how random this really is. We're not using functionality designed for randomness, so this is worth looking in to.
WITH 
  E1(N) AS (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
           ),                          -- 1*10^1 or 10 rows
  E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows
  rand as(select dbo.RandomIntFromId(5) as r from e2)
  select r, count(r) from rand group by r order by count(r) desc
;
My results were as follows:
0	28
1	22
3	17
4	17
2	16
That seems like a decent spread to me, more than good enough to use instead of the build in function, if we wanted to.

Conclusion

Today we looked in to two ways to create random numbers in SQL Server, wrote functions to call to return random values within a range ( although we didn't work on having a minimum value that's not zero, that's a simple exercise if you want to do it ), and checked our code to make sure that the spread of values was reasonable. Random values are not something you need often in SQL, but when you do, it's good to know how to create them efficiently and cleanly.

License

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

Share

About the Author

Christian Graus
Software Developer (Senior)
Australia Australia
Programming computers ( self taught ) since about 1984 when I bought my first Apple ][. Was working on a GUI library to interface Win32 to Python, and writing graphics filters in my spare time, and then building n-tiered apps using asp, atl and asp.net in my job at Dytech. After 4 years there, I've started working from home, at first for Code Project and now for a vet telemedicine company. I owned part of a company that sells client education software in the vet market, but we sold that and I worked for the owners for five years before leaving to get away from the travel, and spend more time with my family. I now work for a company here in Hobart, doing all sorts of Microsoft based stuff in C++ and C#, with a lot of T-SQL in the mix.

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 23 Jan 2014
Article Copyright 2014 by Christian Graus
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid