Picking Random MS SQL

Getting a random row in MS SQL from large tables

1619 VIEWS

· · · ·

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.

SELECT TOP 1 column FROM table ORDER BY NEWID()

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 )


Chris Riley is a technologist who has spent 12 years helping organizations transition from traditional development practices to a modern set of culture, processes and tooling.


Discussion

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 *

Menu