SQL SERVER – Selecting Random n Rows from a Table

Every day I spend a good amount of time with different customers helping them with SQL Server Performance Tuning issues. Today we will discuss the question asked by a developer at the organization where I was engaged in Comprehensive Database Performance Health Check. The developers wanted to know if there is any way he can randomly select n rows from a table.

This question is indeed very interesting as so far we have heard that is they is a way to retrieve TOP or BOTTOM rows but in this case, they wanted to retrieve random rows from any particular table. Additionally, they wanted to make sure that whatever the row they select ones, the same set of rows should not be selected next time and the rows should be truly random.

Honestly, it is possible to retrieve random rows from any tables. Let us see a simple example on the AdventureWorks database.

USE AdventureWorks2014
GO
SELECT TOP 10 *
FROM [Production].[Product]
ORDER BY NEWID()
GO

When you run the above code every single time you will see a different set of 10 rows. The trick is to add ORDER BY NEWID() to any query and SQL Server will retrieve random rows from that particular table.

I find this method very effective and would love to know your feedback about the same. Do let me know if you use any other trick and I will be happy to blog about retrieving n rows from any table randomly.

Reference: Pinal Dave (http://blog.SQLAuthority.com)