How to Get Random Records from Table? – Interview Question of the Week #105

Question: How to Get Random Records from Table?

Answer: This question seems to be never get old. I have previously answered this question multiple times on my blog, but I still get this question from many readers who prefer sending email over doing searches on Google.

There are many ways to retrieve random records, here is the one method which I often use it as I can remember the syntax of the same easily.

-- First Execution
SELECT FirstName, LastName
FROM [Person].[Person]
ORDER BY CHECKSUM(NEWID());

You can see in the image when I ran the above script multiple times, every single time it gave us different result. This is because we have used the NEWID() in the order by and it brings back different results every single time.

Additionally, please note that above query can be quite expensive if you have tables with hundred thousands of the rows.

Here is the additional link for you to refer to the same subject.

• Retrieving Random Rows from Table Using NEWID()
• 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

Let me know if you are aware of any other method and you believe that is more efficient than the method displayed in this blog post. I will be happy to do another blog post with due credit to you.

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