Data Management

Generate random sets in SQL Server with NewID()

Even though SQL Server was not designed to produce random sets, Arthur Fuller explains how the function NewID() provides you with a simple way to generate random sets.

Our team recently completed a project for a travel-package organization, for which we received glowing thank-you letters and e-mails from very happy customers. We stored these praises in a table called Kudos.

During the Web site design phase, we decided to take advantage of this Kudos table by placing some of the customer feedback on a Web page. We chose to display 10 notes, but we didn't want a fixed display because it would be boring and require maintenance. So, we opted to display 10 notes selected at random. I'll show you how to generate random sets in SQL Server.

Since you may not have a table named Kudos, let's switch to one you do have: Northwind. The obvious T-SQL function fails to meet the mark:

select rand(), productid, productname from Products

The problem with this method is that the Rand() function returns the same value for all rows within a given call, which you can see in Listing A.

You also won't get the result you want by seeding Rand(). You can supply a seed value to the Rand() function. In the following code, we attempt to seed the Rand() call with the productid obtained from each row.

select top 10 
rand(productid) as row, productid, productname
from Products
order by row

This returns the result set in Listing B.

No matter how many times you execute this command, even though the randomly assigned row returns unique values, we still get the productids one through 10. So, this approach will not work.

However, there is another function that will prove more useful: NewID(), which returns a GUID (Globally Unique Identifier). Let's see what happens if we substitute the Rand() function with NewID():

select top 10 
newid() as row, productid, productname
from Products
order by row

This produces a result set similar to Listing C.

Your results will differ, since NewID() manufactures a GUID each time it is called. Therefore, if you run the above command two or three times, you will get different result sets.

The solution is to call NewID() as one of your columns, and then order the group by NewID(). The solution is breathtakingly simple, and you can apply it to numerous situations. For instance, you might want to create a list of featured products, or 10 random customers, or 100 randomly selected orders with their details.

SQL Server was not designed to produce random sets, but its design began in the world of mathematics and not commerce. Thanks to the function NewID(), generating random sets is not only possible but effortless.

Miss a tip?

Check out the SQL Server archive, and catch up on the most recent editions of Arthur Fuller's column.

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!

Editor's Picks

Free Newsletters, In your Inbox