Fetching Random Rows From SQL Se

Tip: Fetching Random Rows From SQL Server

Recently I needed to fetch random rows from a SQL server table. If you have an integer column then using RAND() function goes well. However in my case there was no number column. In such cases you can use the following query:

SELECT TOP <n> <column list> 
FROM <table> 
WHERE <criteria> 
ORDER BY NEWID()

The key is the use of NEWID() function that returns a GUID. An example query would look something like this:

SELECT TOP 10 * 
FROM Employees
ORDER BY NEWID()

This way is also useful for selecting data for testing purposes.


Bipin Joshi is an independent software consultant and trainer by profession specializing in Microsoft web development technologies. Having embraced the Yoga way of life he is also a yoga mentor, meditation teacher, and spiritual guide to his students. He is a prolific author and writes regularly about software development and yoga on his websites. He is programming, meditating, writing, and teaching for over 27 years. To know more about his private online courses on ASP.NET and meditation go here and here.

Posted On : 23 July 2008


Tags : ADO.NET Data Access SQL Server