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

Readers still send me this interview question even though I have answered it before: how do you return random records from a table? One reason I like the question is that the short answer is easy to remember, while its cost is easy to forget.

A simple random row query

In an AdventureWorks database, run this query twice. The order can differ between executions because NEWID() supplies a value for each candidate row. TOP returns only the requested number of rows.

SELECT TOP (5) BusinessEntityID, FirstName, LastName
FROM Person.Person
ORDER BY NEWID();

My older example used ORDER BY CHECKSUM(NEWID()). That also changes the order between runs, but the checksum compresses a GUID into a smaller value and can produce ties. ORDER BY NEWID() states the intent directly. Neither expression promises the same result on a later execution, so do not use this as a stable page order.

The original three-run demonstration

My earlier all-row example used this CHECKSUM ordering. Its result is still useful: three executions show different leading rows. The current TOP example above limits the output and avoids compressing the GUID.

-- Run each SELECT as a separate batch.
SELECT FirstName, LastName
FROM Person.Person
ORDER BY CHECKSUM(NEWID());
GO
SELECT FirstName, LastName
FROM Person.Person
ORDER BY CHECKSUM(NEWID());
GO
SELECT FirstName, LastName
FROM Person.Person
ORDER BY CHECKSUM(NEWID());

Do not ignore the cost

SQL Server must assign ordering values across the qualifying rows and choose the first five after that random ordering. On a large table, the scan and sort can be expensive even though the output contains only five rows. Filter to the intended candidates first, and check the actual plan and logical reads on representative data before using this in a frequent production request.

If you need a rough page-level sample instead of an exact count, TABLESAMPLE is a different tool with different sampling behavior. Do not assume that it chooses every row with equal probability. The interview answer should first clarify whether the caller needs a quick demo, an exact number of random rows, or a statistically defensible sample.

If you have a more efficient method for your workload, share the conditions that make it work. I will be happy to examine it and give due credit. For related examples, see my earlier NEWID() sample, random number range question, and random number generator script.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.