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 uniqueidentifierdata 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 uniqueidentifierwhose 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 creating 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.

TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!