title: Performant stable random sort in SQL Server?
body: <p>I want to query a rather large table (millions of rows), providing a seed value, in a manner that will guarantee a random order - but one that remains stable across multiple queries as long as the same seed is used. </p>
  
  <p>The best I've come up with so far is</p>
  
  <pre><code>SELECT TOP n *
        FROM tbl t
    ORDER BY t.int_column % seed, t.int_column
  </code></pre>
  
  <p>Is this a usable approach, both from a performance standpoint and a somewhat-uniform distribution of result rows over different seeds?</p>
  
  <p>Edit:</p>
  
  <p>For context, the stable sort is needed because of multiple - possibly nested - <code>WHERE NOT IN</code> queries that operate on the same data set; e.g.</p>
  
  <pre><code>SELECT *
    FROM tbl t
   WHERE t.some_criteria = 'some_value'
     AND t.id NOT IN
  (
      SELECT TOP n t.id
            FROM tbl t
           WHERE t.some_other_criteria = 'some_other_value'
        ORDER BY t.int_column % seed, t.int_column
  )
     AND t.id NOT IN
  (
      # etc.
  )
  </code></pre>
  
  <p>When the order of the subselects is random, but not stable (i.e. <code>NEWID()</code>, <code>TABLESAMPLE()</code>), the result rows fluctuate wildly between executions. </p>