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

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 res开发者_JAVA百科ults 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.

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

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

I was struggling with this same problem. I solved it with CROSS APPLY and TOP. Keeping in mind that CROSS APPLY pulls my outer table into scope for the derived table, I knew there had to be a way to do this.

The following code results in 3(*) random related products being added based on the manufacturer.

INSERT INTO     ProductGroup (
                    ParentId,
                    ChildId
                )
SELECT          DISTINCT
                P.ProductId,
                CandidateInner.ChildId
FROM            ProductRelated PR 
JOIN            Product P
ON              PR.ChildId = P.ProductId
CROSS APPLY     
                (   
                    SELECT      DISTINCT TOP 3
                                NewId() AS RandId,
                                Product.ManufacturerId,
                                ProductRelated.ChildId 
                    FROM        ProductRelated 
                    JOIN        Product 
                    ON          Product.ProductId = ProductRelated.ChildId
                    WHERE       ManufacturerId IS NOT NULL
                    AND         Product.ManufacturerId = P.ManufacturerId
                    ORDER BY    NewId()
                ) CandidateInner
LEFT JOIN       (
                    SELECT      DISTINCT TOP 100 PERCENT
                                ParentId,
                                COUNT(DISTINCT ChildId) AS Ct
                    FROM        ProductGroup 
                    GROUP BY    ParentId
                    HAVING      COUNT(DISTINCT ChildId) >= 3
                ) AlreadyGrouped
ON              P.ProductId = AlreadyGrouped.ParentId
WHERE           P.ProductId <> CandidateInner.ChildId
AND             AlreadyGrouped.ParentId IS NULL
ORDER BY        P.ProductId

*Note that this will insert fewer than 3 in the following 2 cases:

1) Where there are < 3 products related by manufacturer 2) (Problematic) Where the random top 3 returns the same product to itself.

(1) above is unavoidable.

The way I handled (2) above was to run this twice then delete duplicates. This is still not 100%, but statistically, it's more than sufficient for my requirement. This is in a nightly-run script, but I still like the speediness of having the <> outside of the CROSS APPLY - anything pulling that check in scope results in scans of the derived tables resulting from the manufacturer join, even though pulling it inside will mean that (2) is no longer an issue, but it's painfully slow vs. instant with proper indexes.