[thelist] Free SQL Tip

[Ken Schaefer]

A caveat:

Generating GUIDs is not a trivial operation, and so doing a
SELECT...FROM table that is going to cause a lot of GUIDs to be
regenerated will adversely affect performance.

www.adopenstatic.com/faq/randomrecord.asp (if anyone also wants
matching code for Access)

Cheers
Ken

On Tue, 31 Aug 2004 14:50:45 -0400, Joshua Olson <joshua at waetech.com> wrote:
> <tip type="SQL" author="Joshua Olson">
> Here's a great way to get fields from a table back in random order on SQL
> Server.  First, add a new column, call it "rank".  Set it to data type
> "uniqueidentifier" and then set its formula to "(newid())".
> 
> Then, simply ORDER BY this new columns.  Example:
> 
> SELECT id, name, eyeColor
> FROM people
> ORDER BY rank
> 
> The results will be in random order every time!  The randomness of the
> results is much better then if you try to use random numbers and seeds, as
> found in other techniques.
> 
> If you don't want to add a new column, you can also ORDER BY newid(),
> example:
> 
> SELECT name, age, weight
> FROM othertable
> ORDER BY newid()
> </tip>