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

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

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 1
SELECT TOP 100 *
FROM Sales.SalesOrderDetail
ORDER BY NEWID()
GO
-- Method 2
SELECT TOP 100 *
FROM Sales.SalesOrderDetail
ORDER 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.

Watch a 60 second video on this subject

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

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

About these ads

17 thoughts on “SQL SERVER – Retrieving Random Rows from Table Using NEWID()

  1. Hi,

    Its very nice to learn the things related to Random Number. Also, i read the previous articles to its links. Its very use for Quiz like real time programs.

  2. Not entirely sure how example one differs from example 2. Why is checksum() required when you are already providing a random set of data?

  3. Pingback: SQL SERVER – Generate Random Values – SQL in Sixty Seconds #042 – Video « SQL Server Journey with SQL Authority

  4. just for curiosity, referred this linke “http://msdn.microsoft.com/en-us/library/cc441928.aspx” to see how NEWID() works to get random rows. Here NewID() function generates a GUID for each rows and then do the sorting by that GUID, and then finally it returns the result as demands (like top 10, top 20..etc). Assigning GUID for each rows is a time taking and more disk I/O utilization process, When same result could be achieved by assigning row number for each row instead of GUID. Could you please explain why this is done using GUID and not rownumber.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s