I have also not had time to benchmark performance, but here is another technique that gives the illusion of randomisation:
ORDER
This uses the text in a column from the query, reverses it and removes spaces, and then extracts a randomly selected character to order the results.
Obviously this is limited to situations where you have a text column to work with...
Minor point... At the end of the article where you say you could set the uniqueID column as a pkey I think you mean to set it as a clustered index thus allowing returning of rows in the GUID order without any sorting effort. Common to mix up the pkeys & the clustered index of a table as they often tend to be one and the same if the defaults are accepted
It is tricky getting random data in a set-based fashion. Perhaps you could do something along the lines of
1. Having a unique row number for each row in your table without gaps - an identity column would be perfect2. Indexing by this column either clustered or using at least a covering index3. Use some mechanism to generate row numbers to select. Perhaps the pairs of digits in a generated GUID (or triplets, quads, etc depending on how many rows you have)....
Step #3 might be the lengthy & wasteful bit though..... Will give it some more thought
When I first started to read this, I thought "Jeez... that's a little obvious, Andy. Hardly worth an article." Now that I see how some folks are trying to randomize data, I have to admit that such a short and sweet article about it is well overdue.
I don't believe that anyone will come up with a method that is either faster or more consistently random than just doing the ORDER BY NEWID()... but it is fun to watch folks try.
Using "ORDER BY CHECKSUM(NEWID())" rather than "ORDER BY NEWID()" apparently produces values with a better random distribution.
