Oh, Right, Microsoft Security

I'm not a real big Microsoft basher by nature. However, I'd like to take this moment to mention that the SQL Server RAND function sucks.

And not just in a, "gee, that's kinda bad", way. More in a, "WTF is the point", way.

Here's the problem: select 10 records at random from a list. Not a big deal, right?

Here's how you do it in Oracle:

SELECT column
FROM ( SELECT column FROM table ORDER BY dbms_random.value )
WHERE rownum < 11

Because of the TOP syntax, MS SQL should be easier, right?

SELECT TOP 10 column FROM table ORDER BY RAND()

Well, this simply doesn't work. You can seed the rand all you like, feed it bits of the data, wrap it in sub queries, it's just useless.

I'll spare you the myriad of solutions that can be tried. The most likely is to generate a temp table and then loop through with the RAND(), that seemed promising, but much overkill.

Here's the final solution

SELECT TOP 10 column FROM table ORDER BY NEWID()

Not real intuitive, but I'll take it. SQL Server 2005 looks really cool. Hopefully we'll get it early, because this is sad.

Note, a strong random generator is required for security hashes, hmm...