Problem using ROW_NUMBER() to get records randomly (SQL Server 2005)

Question

Hi all,

I want to get 1000 records from a table randomly, so I use:

SELECT top 1000 
       mycol1
     , mycol2
     , ROW_NUMBER() OVER (ORDER BY NEWID()) rn
FROM mytable

However, I don't want to see rn in my resultset, so I do:

SELECT mycol1
     , mycol2
FROM (
    SELECT top 1000 
           mycol1
         , mycol2
         , ROW_NUMBER() OVER (ORDER BY NEWID()) rn
    FROM mytable
) a

When I do this, the results do not come randomly anymore. They come as if I just said top 10000 without randomization using row_number().

When I change the query to

SELECT mycol1
     , mycol2
     , rn
FROM (
    SELECT top 1000 
           mycol1
         , mycol2
         , ROW_NUMBER() OVER (ORDER BY NEWID()) rn
    FROM mytable
) a

they are random again.

I guess sql server does some kind of optimization, saying "hey, this guy doesn't need the column rn anyway, so just ignore it". But this results to an unexpected behavior in this case. Is there any way to avoid this?

PS: I use the ROW_NUMBER() trick because mytable has 10 mio. rows and

SELECT top 10000 *
FROM mytable
ORDER BY NEWID()

runs forever, whereas with ROW_NUMBER() it takes only up to 30 secs.

Answer 1

You could try using RAND to order the entries then limit them to 1000 results:

SELECT mycol1, mycol2
FROM mytable
ORDER BY RAND()
LIMIT 1000

Answer 2

You could also try using the rn field in some petty where clause like

WHERE rn > 0 in your outer query which would maybe force the compiler to bring the RN field through.

Also I think your overall query is going to be an issue if you want to randomly sample your entire millions of records. This will only grab the "first off disk" block of records which while not guaranteed to be the same will more often than not be the same 10000.

I would suggest creating a set of 10,000 random numbers between MIN(PrimaryKey) and the MAX(PrimaryKey) and then doing a WHERE PrimaryKey IN (...) or similar

Answer 3

Add something like Where rn Is Not Null to the outer query so rn it is included in query plan and not optimised out