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 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)
Hi,
Could you detailed description?
What did you mwan by detailed description?
I was unable to distinguish the difference between method A and method B.
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.
Simple and great. Thanks.
I knew this.
Simple but not great. Common anti pattern. Solution is not to use ORDER BY. Try TABLESAMPLE
When would you need to use Method 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?
I didn’t understood whats the difference between Method A and Method B.
Can you explain in detail?
[...] Retrieving Random Rows from Table Using NEWID() [...]
Pinal Sir,
In newid() how to avoid duplicate values