SQL SERVER – Retrieving Random Rows from Table Using NEWID()
- 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 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.
Reference: Pinal Dave (http://blog.sqlauthority.com)
Filed under: PostADay, SQL, SQL Authority, SQL Function, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
