Getting a Random Record Using a Stored ProcedureBy Nathan Pond
There came a time when I was required to grab a random record from a table for use in ASP. I immediately thought of using a stored procedure, and since my database was MS SQL Server I had the luxury of being able to write a stored procedure. In this article I'll show you how, and explain what I did.
If you are not comfortable writing stored procedures please see Writing a Stored Procedure Part I and it's followup Part II. If you are interested in some alternative ways to grab a random record from a database be sure to read: Choosing a Random Record from a Recordset and Returning a Random Number of Database Records. Both of these methods do not use stored procedures, so if you do not have the luxury of stored procedures, or don't want to mess with them, be sure to review those two articles. (If you are interested in returning a complete recordset, but having its rows in a random order, be sure to read: Returning Rows in a Random Order!)
The trouble I had with the Choosing a Random Record from a Recordset article was that my table had close to 1 million records in it, and the site had a lot of traffic. To download the complete 900,000 records everytime and then use ASP to sort through the recordset would have been a huge performance hit. However, this method is good for Access users and small tables.
|Need more than One Random Record?|
|This article demonstrates the most efficient way to grab a single random record from a table. If you need to grab, say, 10 random records from a table, be sure to read: Returning a Random Number of Database Records!|
Ok, now onto the code, I'll let you see what I did and then explain it.
That's all there is to it. The first thing I did was create a temporary table with the columns I would need,
in this case
field3; along with
as an identity (auto-increment). I then inserted all of the records from
myTable into the
temporary table. The logic here is that each row will be in the temp table, and each will have its
own unique id.
When the temporary table is filled the
idNum column, an
will start at 1 and increment by 1 each time, ensuring that no numbers are skipped.
I had to do this because
myTable had some records
deleted in the past, therefor the id numbers would sometimes skip, say from 55 to 57. Meaning that if
the random number (which we will generate to determine which record to get) turned out to be 56, no record
would be returned beause the record that had the id number of 56 was deleted. Since no id numbers are skipped
in this temporary table, we can now get a random number between one and the number of rows in
#TempTable, and select the row with that id.