RAND() function in SQL Server is a basically a pseudo random number generator. In reality, there is no such thing as a random number but using this function allows you to retrieve a randomly generated
FLOAT value between 0 and 1. Though the uses for this function are somewhat limited I recently spent some time tinkering with this function in an attempt to select random values from a table.
Why, you might ask? Well, first and foremost I was bored and looking to kill some time. Though I didn’t set out to build anything “useful”, I ended up with an interesting tool for randomly selecting restaurants from a table I had populated. In the end I thought, at the very least, this could make an interesting post detailing how to better use the
RAND() function to work with actual data, not just random
Before I get around to explaining the details of the script we’ll need to create a database table to store the data that will be referenced. As mentioned above, I’m illustrating this concept by using the RAND() function to randomly select a restaurant from a table containing a list of restaurants.
The following script will create the necessary table.
/*Create Restaurants Table*/
CREATE TABLE Restaurants (
ID INTEGER IDENTITY(1,1) PRIMARY KEY,
This script will create a table named Restaurants with three columns. The first is an auto-incrementing identity column to act as the primary key. Though having a PK for this example is probably overkill, it’s never a bad idea to keep best practices in play. The
R_Name column stores the restaurant name and the
FREQUENCY column will keep track of how often that record is retrieved.
Once the table has been created the next step is to populate it with some data. The following script will insert 10 records into the newly created Restaurants table.
/*Add Restaurants to Table*/
INSERT INTO dbo.Restaurants
('In-N-Out Burger', 0),
('Five Guys', 0),
('Carls Jr', 0),
('Ruby Tuesday', 0),
('Bad Daddys Burger Bar', 0),
('McAlisters Deli', 0),
('East Coast Wings', 0),
('Jersey Mikes', 0)
Once you have run the above script you should see the following data in your table.
Now that the table has been populated it’s time to take a look at how we can use the RAND() function to pick a random (pseudo-random) record from the table.
I have already taken the necessary steps to create a stored procedure out of the following script. I find this is more convenient than just having a
SELECT script as it is easier to call the procedure than selecting the body of the script and running it, especially when I was running it over and over as I was experimenting with the logic.
CREATE PROCEDURE [dbo].[pick_restaurant]
DECLARE @count INT,
SET @count = (SELECT COUNT(r_name) FROM restaurants)+1
SET @result = (SELECT FLOOR(RAND()*(@count-1)+1))
SET @freq = (SELECT FREQUENCY FROM Restaurants
WHERE ID = @result)+1
SET Frequency = @freq
WHERE ID = @result
SELECT r_Name, Frequency FROM dbo.restaurants
WHERE ID = @result
Once the procedure has been created you can call it using the following script. Go ahead and run it a few times. This will begin populating the FREQUENCY column with the number of times any given record has been retrieved.
To better understand what this procedure is actually doing let’s break it down into four parts. The
DECLARE I have create three parameters,
INTEGER values. These will be used by the next part of the script to preform some basic calculations which will result in the random record being selected.
In the second part of the script I am setting the value of each parameter. For
@count I am simply counting the number of records that exist in the restaurants table and adding 1 to that value. This allows all records to be considered when the script executes.
@result SET is where the
RAND() function is being called. Here I am calling the
RAND() function alongside the
FLOOR function in order to ensure that 0 is initially returned before the additional calculations are made. This essentially insures a non decimal value is being used. The tail end piece of that code then takes the
@count parameter and subtracts 1, then adds 1 after the closing parenthesis. This adds some additional logic to the random number generation process to further randomize as well as insure that all records are available as possible result set.
SET command determines the new frequency value for the record that was selected by simply selecting the current value and adding 1.
The third piece of the script simply updates the
FREQUENCY column based on the
@freq value that was determined in the previous step.
Finally the script returns the
Frequency of the record selected by the script. After running the script numerous times you can see that the frequency is being updated each time a particular record is called.
The more data you include in your data set (or table in this case) the more variable the
RAND() function becomes.
Though I struggle to see a real world solution for this I thought it was an interesting experiment and a fun way to utilize the
RAND() function in a way that wasn’t just returning a random decimal number. I hope this post has provided you with some insight or possibly a solution to a particular problem you were working with.