Bit of an un-usual one but if you want to return a result set in a random order then you can use this;

SELECT *
FROM table
ORDER BY NEWID()

NEWID creates a unique value every time it's executed, the values are non-consecutive.