Returning Rows in Random Order (SQL 2000 on Win2000)
How about this:
use pubs go
select au_lname,newid() from authors order by newid() go
I believe the Windows api which implements newid() no longer returns sequential guids in Win 2000. I've tested this using SQL 2000 on Win 2000. Doesn't work using SQL7 on NT4. Haven't tried SQL7 on Win2000.
Another way to do this is to use the middle part of the random number that is returned. The following example uses about 6 digits starting from three in (exactly 6 from 3 if you use 1000 instead of 1024). Using RAND() is likely to be cheaper than using trigonometric functions.
SELECT ID, RAND(FLOOR( (RAND(ID)*1024-FLOOR(RAND(ID)*1024)) * 1048576) ) FROM TheTable ORDER BY 2