SQL Server Performance Forum – Threads Archive

Alternative for random sort

Is there any alternative solution for sorting records randomly without using ORDER BY NEWID()? Any ideas will be highly appreciated. Thanks,
Jon M

---

http://www.sqlteam.com/item.asp?ItemID=217 HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

If you don’t like cursors, you could do something like select rand((10000.0000 * rand() * Table_ID)), Table_ID
from Your_Table
order by 1 I don’t know the validity of how random the order generated is, but it doesn’t produce a consistent list. The rand() gives you a random multiplier each time you run it and therefore a different seed each time. If you want a truely random order this may not work, but if you want a list ordered differently each time this gives you an answer. (I wouldn’t have thought it would be any quicker than using newid() though.) Why the aversion to newid()? Regards, Robert.

---

Why do you want not to use RowId()? Because it is useful if the table has millions of records.
Madhivanan Failing to plan is Planning to fail

---

]]>