Retrieving a Random Record using SELECT Statement[SQL Server]

When we were developing a small client server application using C# and SQL Server, we had a requirement to select a record randomly from a database table and show it to the user.

First we tried bringing a set a qualifying record to front end and randomly pick one using C# code. But this is proven as a very inefficient way as unnecessarily records are being fetched from database and discarded after selecting one random record.

We started looking for an efficient way through which a random record can be picked using SQL script and fetch that record alone out of database.

We succeeded to pick the random record using a simple SELECT statement trick. All we need to do to randomly pick a record is, SELECT TOP 1 record from the table and order it using NEWID() function. Here is the sample code

SELECT TOP 1 Id, FirstName, LastName, Age, DateOfBirth

FROM Employee

ORDER BY NEWID()

 

In order to test how good this method is going to randomise the data, a small test is performed on a table containing 429 records.  The results are pretty impressive and the data is properly randomised in the 100 test runs as shown in the following chart.