I have previously written about how to get random rows from SQL Server. Here is a new trick to get random rows from table data with NEWID().
- 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.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
24 Comments. Leave new
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?
You can use it if you want to create random numbers
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?
It is just a different method. Method1 will order by hexadecimal values while Method2 will order by number
Pinal Sir,
In newid() how to avoid duplicate values
It is guaranteed for distinct values
Hello , Is one of the two methods return duplicate rows or both of them get distinct rows
@Soumya, @Hoda:
NEWID() will generate a GUID (global unique identifier) so there won’t be any duplicate values, in neither of the queries.
i want to Order by using newid and then sort the result is it possible
Order by newid(), othercolumn
how to get 3rd record and 9th record from a table by single query
You need to generate a row number and then filter 3rd and 9th row
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.
For row number, you need to order it by the specific column, the examples shown are for random ordering where you need to use NEWID()
To know hot to reset random ordering for each group, refer this post
Hello Sir,
Is newId() always give the unique record for single query
I want to get random quiz details from that function can i use this?