Without further adieu:
select
convert(int, 1 + 400 * RAND(CHECKSUM(NEWID())))
So, if you’re wondering what’s going on there, I’ll break it down.
First of all, we can’t make use of the RAND function on its own. When called repeatedly in quick succession it will actually produce the same number. If you give it a seed as a parameter, it uses that same seed (and therefore generates the same “random” number) for each row that is returned.
The only method that I am aware of in SQL that generates something unique is the NEWID method, but this isn’t the type of data we want as it produces a GUID suitable for unique identification.
BUT!…if we wrap NEWID with CHECKSUM we get an integer, a random integer, to work from.
Unfortunately, this isn’t exactly what we need either, as we have no control over the range of values produced.
HOWEVER!…we can reintroduce RAND to the equation and get us a float between 0.000 and 1.000. Nice. Multiply this by the range you want, then add 1 (because of the way RAND works, it will never generate a 1.000 so you’ll never get the max value in your range).
Basically, the above code will generate a random number between 1 and 400 with really good distribution. I was able to achieve 99.9% unique on 10,000 samples with 100,000 range.
Change the 400 to whatever you like and away you go.
I remember reading an article on wired a while ago that there were serious security concerns with the predictability of random number generators. I believe it was something about the fact that there is no true random number generator in existence yet. Randomness must be a very strange factor for a machine based purely on logical rules. (even though the programmers may not always be logical)
ReplyDeleteI was about ready to give up on finding a way of randomising.
ReplyDeleteThis method is perfectly random enough for my needs, infinitely better than rand on it's own.
Works for me.
ReplyDeleteThis is the best solution I've seen yet.