I must say that I have never used the random functionality in SQL more than once in my life and that was not for work, it was in a hobby project. Never the less, I find this article (free registration required) on how the RAND function works and is seeded quite interesting.

A summary of the article: You want a random number for each row in a result to do some kind of filtering. The problem is that the RAND function by default uses date/time to seed the random so in a query you don't really get any different random numbers. So you need a nifty way to seed the random for each row. The article proposes: RAND(Checksum(Newid()))

The use of Newid in the link above might however not be practical for performance reasons. If you want to try some other ways to produce random you can find more alternatives here.