Generating Random Numbers For All Rows in a Query and Other Funky Stuff You Can Do With Them

UPDATE: Check out Generate a Random Number for Each Row in a Query {a better way} for an alternative method that I now use for generating random numbers using CRYPT_GEN_RANDOM

Here’s a lunchtime quickie for you all, this is something that I’ve seen asked on the forums plenty of times and always gets some quite convoluted responses.

How do we get SQL Server to give us random numbers for each row returned by a query?

Well that’s an easy one isn’t it?  We can use RAND().

My major gripe with RAND() is what happens if we want to generate a random number for every row of a query, lets see what happens if we run the following against our trusty old workhorse, AdventureWorks and try to assign a random number to everyone in the person table.

SELECT FirstName,MiddleName,LastName, RAND() AS RandomNumber
FROM Person.Person

Hmmmmm…. It looks like we’ve got the same number for every person, that wasn’t what we wanted.  And that’s my issue with RAND(), it’ll give you a different random number every time it runs but if run as part of a query it’ll always return the same number for every row returned.

So what else can we do?  We’ll there is something that gives us a ‘random’ value for every row in the query and that’s our good old friend NEWID().  Let’s try the same query but this time we’ll swap RAND() with NEWID().

SELECT FirstName,MiddleName,LastName, NEWID() AS RandomNumber
FROM Person.Person

Now, that’s looking better, we’ve now got uniquely random values for each of the people…  but I still don’t like that, it’s a horrible GUID, what am I supposed to do with that?!  Wouldn’t it be nice if we could get an ordinary looking number out?  Perhaps CAST or CONVERT it to an INT?  I’ll tell you now, that’s not going to work.  But there is something that we can do to make things better, CHECKSUM().  Now CHECKSUM() is a little known function that’s designed to return a checksum or hash value for a given expression.  The nice thing for us is that the checksum returned is a numeric value.  Let’s have a look and see what that’s looking like now….

SELECT FirstName,MiddleName,LastName, CHECKSUM(NEWID()) AS RandomNumber
FROM Person.Person

That’s looking better, now we’ve got a number!  You know what?!  I’m still not happy (I know I’m a difficult guy to please), I don’t want to be seeing those nasty negative numbers.  But once again SQL Server can come to our rescue, this time with the ABS() function.  ABS() returns the absolute value of a number or for you and me, it gets shot of that minus sign.

SELECT FirstName,MiddleName,LastName, ABS(CHECKSUM(NEWID())) AS RandomNumber
FROM Person.Person

And there we have it, a different random number generated for each row of our result set.

Wanna Take It Further, What About Generating Numbers In A Range?

Specifying a Maximum Number

But what if we don’t want those crazily big numbers, what if we only want to generate numbers up to say 10?  Well for that you’re going to have to think back to your school maths class (when you weren’t throwing pens at the class nerd or chatting up the fit blonde….  or, if you were anything like me, you were the one dodging those pens) and remember a little thing called modulus.  Basically, using modulus, we can get the remainder of a number after it’s been divided by a specified value.

So if we want to return a random number between 1 and 10 all we have to do is mod our number by 10 and add one (we need to add one because 10 mod 10 would really give us numbers between 0 and 9 and that’s not what we really want at all).

Let’s try that out….

SELECT FirstName,MiddleName,LastName, ABS(CHECKSUM(NEWID()))%10 + 1 AS RandomNumber
FROM Person.Person

Nice!

What about  if we want a range of values?

You might want to generate some random numbers in the range of 10-20.  That too is very simple, all you need to do is mod by the number of values in your range and add to that that lowest number of the range plus one.  For example, for a range of 10-20, you’ve got 10 values in that range and with the lowest value being 10, you could use the following…

SELECT FirstName,MiddleName,LastName, ABS(CHECKSUM(NEWID()))%10 + 11 AS RandomNumber
FROM Person.Person

Generating a number within a range

ABS(CHECKSUM(NEWID()))%<Number of Values in Range> + <Lowest Value in Range> + 1

A Few Funky Things You Can Do With Random Numbers

Random Day 

This is something that I’ve used many times for various reasons, a random day of the week can easily be generated using the following code.

SELECT FirstName,MiddleName,LastName, DATENAME(WEEKDAY,ABS(CHECKSUM(NEWID()))%7 + 1) AS RandomDay
FROM Person.Person

Obviously you could expand on that and generate random months or even full dates if you really wanted to.

Just For Fun – A SQL Based Card Game

Just for fun, why not put together a SQL based game of cards?  A table could hold the cards with an id and then a random number could easily be generated to choose what cards you were dealt…  You know what, I might even thing about seeing if I can put that together….

Well, thanks for reading and I hope that you’ve found this little tip useful 🙂