title: SQL ROW_NUMBER and sorting issue
body: <p>In SQL 2005/2008 database we have table BatchMaster. Columns: 
  RecordId bigint - autoincremental id, BatchNumber bigint - unique non-clustered index, BatchDate). We have sproc that returns paginated data from this table. That sproc works fine for most of the clients, but at one SQL server instance we have problem with records order.
  In general, at sproc we do</p>
  
  <pre><code>select * from
  (
      select row_number() over (order by bm.BatchDate desc, bm.BatchNumber desc) as Row,
      *
      from dbo.BatchMaster bm with (nolock)
  )
  where Row between @StartingRow and @EndgingRow
  </code></pre>
  
  <p>So, as you can notice from the script above we want return records sorted by BatchDate and BatchNumber. That's not gonna happen for one of our client:
  <img src="https://i.stack.imgur.com/1mfyA.png" alt="enter image description here"></p>
  
  <p>Records are in wrong order. Also, notice first column (Row), it is not in ascending order.</p>
  
  <p>Can someone explain why so?</p>