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())) FROM MyBigTable;