title: Problem using ROW_NUMBER() to get records randomly (SQL Server 2005)
body: <p>I want to get 1000 records from a table randomly, so I use:</p>
  
  <pre><code>SELECT top 1000 
         mycol1
       , mycol2
       , ROW_NUMBER() OVER (ORDER BY NEWID()) rn
  FROM mytable
  </code></pre>
  
  <p>However, I don't want to see <code>rn</code> in my resultset, so I do:</p>
  
  <pre><code>SELECT mycol1
       , mycol2
  FROM (
      SELECT top 1000 
             mycol1
           , mycol2
           , ROW_NUMBER() OVER (ORDER BY NEWID()) rn
      FROM mytable
  ) a
  </code></pre>
  
  <p>When I do this, the results do not come randomly anymore. They come as if I just said top 10000 <strong>without</strong> randomization using row_number().</p>
  
  <p>When I change the query to </p>
  
  <pre><code>SELECT mycol1
       , mycol2
       , rn
  FROM (
      SELECT top 1000 
             mycol1
           , mycol2
           , ROW_NUMBER() OVER (ORDER BY NEWID()) rn
      FROM mytable
  ) a
  </code></pre>
  
  <p>they are random again.</p>
  
  <p>I guess sql server does some kind of optimization, saying "hey, this guy doesn't need the column <code>rn</code> anyway, so just ignore it". But this results to an unexpected behavior in this case. Is there any way to avoid this?</p>
  
  <p>PS: I use the ROW_NUMBER() trick because mytable has 10 mio. rows and </p>
  
  <pre><code>SELECT top 10000 *
  FROM mytable
  ORDER BY NEWID()
  </code></pre>
  
  <p>runs forever, whereas with ROW_NUMBER() it takes only up to 30 secs.</p>