David Fowler shows how to generate a random number for each record in a result set:
Hmmmmm…. It looks like we’ve got the same number for every person, that wasn’t what we wanted. And that’s my issue with RAND(), it’ll give you a different random number every time it runs but if run as part of a query it’ll always return the same number for every row returned.
So what else can we do? We’ll there is something that gives us a ‘random’ value for every row in the query and that’s our good old friend NEWID(). Let’s try the same query but this time we’ll swap RAND() with NEWID().
One major use case for this is sampling data sets for model training and testing: if you pull from a range of 1-10, you could perhaps train against 1-5, cross-validate against 6-7, and test against 8-10. Doing this instead of TOP X% reduces the likelihood of sampling bias.