Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Randomly Retrieve SQL Server Records


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

Problem

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:

SELECT RAND()
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. 

 

Solution

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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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)


Learn more about SQL Server tools