Tuesday, September 9, 2008

Selecting a Random Row Using MS SQL

It's often a requirement to choose a random record from a table. This is pretty easy to do in Coldfusion. Select all the rows and do a RandRange(1,query.recordcount) to get a row number. That's great if you have a few rows or even a few hundred. You can cache the query and serve up random rows at lightening speed. But what if you had a few hundred thousand rows?

You can't think of a need like that? How about a contest with a few hundred thousand people entered and you have to decide who gets one of the 15 Macromedia Pens that are left over from the last Max? The good news is that there is a way to do it in MS SQL that is not too costly (if you don't get carried away). Here's the code.
SELECT top 1 *
FROM mytable
ORDER BY newid()
What Is NewId()

"What is this newid() of which you speak?" you ask. MS SQL server creates a guid for each row with each query that it uses internally to track the rows - independent of the primary key. The guid is apparently created with each execution (correct me if I'm wrong) and you can return it using the "new()" function. Since it's unique and random in and of itself it produces a different sort order every time. Ordering by new() will always return a random row from the table - at least as far as I could see.
How Fast Is NewId()

The short answer is that it is pretty fast - acceptably fast anyway. I ran the code above on a table with 340,000 records and it executed in under a second in most cases. That's not fast enough to run constantly (at least not for me - although judging by some of the sites out there it would be an improvement), but it is fast enough for something straightforward like selecting the recipients of those awesome pens. Whatever happened to my pen anyway? Oh yeah... the dog. He had a blue toungue for a month. Everybody would stop us on our walks and tell us they thought he might be having trouble breathing.

In any case, thanks to "Taco Fleur" from Pacific Fox web development for this newid() tip on CF-talk. And may I say that Taco is an awesome name. I'm not sure if it's a girl's name or a guy's name, but if he or she ever hooks up with someone surnamed delis she (or he) could be "Taco Fleur-Delis" - which sounds rather like an architectural style. French Mexican maybe .... hmmmmm....

No comments: