SQL SERVER – Retrieving Random Rows from Table Using NEWID()

November 16, 2012 by pinaldave

I have previously written about how to get random rows from SQL Server.

• SQL SERVER – Generate A Single Random Number for Range of Rows of Any Table – Very interesting Question from Reader
• SQL SERVER – Random Number Generator Script – SQL Query

However, I have not blogged about following trick before. Let me share the trick here as well. You can generate random scripts using following methods as well.

USE AdventureWorks2012
GO-- Method 1SELECT TOP 100 *FROM Sales.SalesOrderDetailORDER BY NEWID()GO-- Method 2SELECT TOP 100 *FROM Sales.SalesOrderDetailORDER BY CHECKSUM(NEWID())GO

You will notice that using NEWID() in the ORDER BY will return random rows in the result set. How many of you knew this trick? You can run above script multiple times and it will give random rows every single time.

Note: This method can be very resource intensive for large resultsets.

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