Getting a Random Record Using a Stored Procedure

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.

CREATE PROCEDURE sp_GetRandomRecord AS declare @nRecordCount int declare @nRandNum int -- Create a temporary table with the same structure of -- the table we want to select a random record from CREATE TABLE #TempTable ( field1 varchar(255), field2 varchar(50), field3 int, idNum int identity(1,1) ) -- Dump the contents of the table to seach into the -- temp. table INSERT INTO #TempTable Select field1, field2, field3 From myTable -- Get the number of records in our temp table Select @nRecordCount = count(*) From #TempTable -- Select a random number between 1 and the number -- of records in our table Select @nRandNum = Round(((@nRecordCount - 2) * Rand() + 1), 0) -- Select the record from the temp table with the -- ID equal to the random number selected... Select field1, field2, field3 From #TempTable Where idNum = @nRandNum Go

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 field1, field2, and field3; along with idNum 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 IDENTITY column, 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.

Happy Programming!