Without further adieu:
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.