Presents your  
SQL SERVER E-NEWSLETTER for September 23, 2003 
 
<-------------------------------------------> 
  
SELECTING RANDOM ROWS FROM A TABLE   
  
There are many ways that you can use randomly selected rows; they're 
especially effective when you want to add dynamism to a site. For instance, 
you could randomly select a product to present as Today's Featured 
Product, or QA could generate a random call list to gauge customer 
satisfaction levels.
  
The snag is that SQL doesn't permit the selection of random rows. The 
good news is that there's a simple trick to getting this functionality
to work in SQL.   
  
The solution is based on the uniqueidentifier data type. Unique 
identifiers, which are also called Guaranteed Unique Identifiers
(GUIDs), look something like this:   
  
4C34AA46-2A5A-4F8C-897F-02354728C7B0   
  
SQL Server uses GUIDs in many contexts, perhaps most notably in 
replication. You can use them when normal incrementing identity columns won't 
provide a sufficient range of keys. To do this, you create a column of type 
uniqueidentifier whose default value is NewID(), like this:   
  
CREATE TABLE MyNewTable   
(   
 PK uniqueidentifier NOT NULL DEFAULT NewID(),   
 AnotherColumn varchar(50) NOT NULL   
,   
  . . .   
  
This function is just the ticket to solve our random rows problem. We 
can simply call NewID() as a virtual column in our query, like this:   
  
SELECT TOP 10 OrderID, NewID() as Random   
FROM Orders   
ORDER BY Random   
  
I used this solution recently when I created a Web page that delivers 10
random customer quotes extolling the virtues of the company. Users also
see new quotes each time they visit the site.
  
This is an easy way to add interest to a site. Now that you know how to 
deliver randomly selected rows, you'll look like a SQL star.   
  
Arthur Fuller has been developing database applications for 20 years. 
His experience includes Access ADPs, Microsoft SQL 2000, MySQL, and .NET.   
  
----------------------------------------