I was working on a blog post this weekend that required a list of random numbers. Now, this isn’t exactly an uncommon task, but it’s not as easy as it would seem. The random number function RAND() has a few inherent flaws. And from what I can remember the random functions from most languages have the same issue.

First a few quotes from BOL about RAND()

Returns a pseudo-random float value from 0 through 1, exclusive.

Note: pseudo-random

If a seed is not specified, the SQL Server Database Engine assigns a seed value at random. For a specified seed value, the result returned is always the same.

If you don’t specify the seed it gets selected at random. But that’s only once per query run, not once per row of the output.

Repetitive calls of RAND() with the same seed value return the same results.

Since the seed is the same, each row of output will get you the same “random” value.

SELECT RAND() FROM sys.objects

Every time you run this you will get a different result, but every row is going to be the same. So you could try explicitly passing in a seed value. (Remember that the seed is an INT so be careful that your values don’t go into the BIGINT range.)

SELECT RAND(object_id) FROM sys.objects

This works better. You at least get unique values. Unfortunately when the seeds are close together in value the RAND values are going to be pretty close together too. Also RAND(seed) always returns the same value. So every time you run this you’re going to get the same result set. Not really all that random if you ask me.

So what’s a good solution? Well, I have to admit I had to look this up myself but I found a great suggestion here.

SELECT ABS(CHECKSUM(NEWID()) % 1000) FROM sys.objects

Quick breakdown:

• NEWID(): This function returns a, probably still note completely random, unique identifier (GUID).
• CHECKSUM(): This returns a checksum (INT) value of the value or values passed to it. Since we are passing in a fairly random value we are getting a fairly random value back.
• % (modulo) 1000: This returns the remainder of our random value divided by 1000. Basically this creates a range of values. In this case -999 to 999. If you want a range of -5 to 5 then use % 6. Simple enough. It’s important to remember that the maximum remainder is going to be one less than the value. Hence a range of 0-5 requires % 6. If you want 1-5 then use (% 5) + 1
• ABS(): Returns the absolute value of the whole thing. You only need to do this if you don’t want negative values.

 
And there you go. A list of actually (mostly) random values!

Edit
It’s funny but I was talking with various people about making mistakes on a blog and owning up to them and then today someone pointed out I’ve made a mistake on this post. I’ve corrected the post above. You’ll see the correction in the modulo portion.