Picking Random MS SQL

Getting a random row in MS SQL from large tables


· ·

It was a nightmare finding a solution to my MS SQL problem.  Once I finally found the answer I decided I must share with the world just in case you are struggling too.  The problem is that with large tables in MS SQL using the below Query to return a random row is VERY SLOW.


In my table of 13 Million, and growing, records this will take about 10 min’s to execute.  This is not acceptable.  MS SQL has some cool things, but also some very silly things.  Why they don’t have LIMIT X, N like MySQL, makes no sense to me.  Anyway.  The solution was to find the Max ID in the database and find a random in that range.  This surprisingly turns out to be very fast.  This was a workable solution for me although it is NOT TRULY random.

SELECT TOP 1 Thing FROM thingsdb.dbo.things WHERE ThingID >= RAND() * (SELECT MAX(ThingID) FROM thingsdb.dbo.thing )

is a bad-coder-turned-technology-advocate who understands the challenges and needs of modern engineers, as well as how technology fits into the business goals of companies in a demanding high-tech world. Chris speaks and engages with end-users regularly in the areas of modern AppDev, Site Reliability Engineering, DevOps, and Developer Relations. He was one of the original founders of the developer marketing agency Fixate IO, and currently works as a Sr. Manager in HubSpot’s Developer Relations team.


Click on a tab to select how you'd like to leave your comment

Leave a Comment

Your email address will not be published. Required fields are marked *

Skip to toolbar