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.
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.
Read on for Kenneth’s solution.