Randomly Retrieve SQL Server Records

By:   |   Updated: 2006-06-29   |   Comments (1)   |   Related: More > Functions - System


Have you ever had the need to randomly retrieve a set of records from a database table to display to users?  One way of doing this is to use the RAND() function to generate a random number and then tie this back to your original record set.  The RAND() function works as follows:

Returns result 0.7154366573674853

The problem with using the RAND() function is that it sets the same random value for the entire recordset.  So if we run this query we get the following result with each record having the same RandomValue.

SELECT TOP 5 ProductName, RAND() as RandomValue FROM products ORDER BY 2
ProductName RandomValue
Alice Mutton  0.80020779549998178
Aniseed Syrup  0.80020779549998178
Boston Crab Meat  0.80020779549998178
Camembert Pierrot  0.80020779549998178
Carnarvon Tigers  0.80020779549998178

Another approach would be to to create a User Defined Function (UDF) to return a random number.  The random number would then be calculated for each record in the record set.  This approach would work, but seems a bit complex for what we are trying to achieve. 



A simple solution would be to use the NEWID() function.  The NEWID() function returns a uniqueidentifier for each row of data that is returned.  So to simply retrieve the first 5 rows from the products table in the Northwind database we would issue the following statement.

SELECT TOP 5 productName FROM products ORDER BY NEWID()
The first run returns the following recordset
  • Guaraná Fantástica
  • Tunnbröd
  • Ikura
  • Filo Mix
  • Genen Shouyu

The second run returns a totally different recordset

  • Mozzarella di Giovanni
  • Scottish Longbreads
  • Gustaf's Knäckebröd
  • Louisiana Fiery Hot Pepper Sauce
  • Filo Mix

So you can see achieving a totally random list of records is very straight forward using the NEWID() function.


Next Steps
  • To learn more about the NEWID() function click here
  • If you need to retrieve a random list of records use this function in your SELECT statements
  • Watch out on very large tables to make sure this does not become a performance overhead, one option is to use a WHERE clause to limit the scope of records that you are working with and therefore the result will be much faster.

Last Updated: 2006-06-29

get scripts

next tip button

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips

Comments For This Article

Monday, November 17, 2008 - 6:23:48 AM - Rohit Madhok Back To Top (2215)

When selecting rows randomly from a large table one may refer to http://msdn.microsoft.com/en-us/library/cc441928.aspx

e.g. SELECT top 100 pk_column_id FROM MyTable WHERE (ABS(CAST((BINARY_CHECKSUM(pk_column_id, NEWID())) as bigint)) % 100) < 10

where, 'pk_column_id' is the name of the primary key column (defined as bigint)


Recommended Reading

Concatenate SQL Server Columns into a String with CONCAT()

SQL Server Rounding Functions - Round, Ceiling and Floor

SQL Server 2016 STRING_SPLIT Function

Using FOR XML PATH and STRING_AGG() to denormalize SQL Server data

Deciding between COALESCE and ISNULL in SQL Server

get free sql tips
agree to terms

Learn more about SQL Server tools