Tuesday, 13 August 2013

MSSQL - The random number generator with a random initialization (random seed)

The random number generator function RAND() produces every time the same sequence of random numbers from 0.0000 to 1.0000.

You can initiate it with some number (seed value) and then RAND(SeedValue) will return a different sequence.
But for the same initialization value RAND() will generate the same sequence of random numbers.
To get a different sequence every time we need each time a different initialization value.
To do this, we can use a combination of NEWID() and CHECKSUM():
SELECT RND=RAND(CHECKSUM(NEWID()));

For example:
SELECT RND=RAND(CHECKSUM(NEWID())) FROM MyBigTable;