title: Paging in nHibernate against SQL Server: Is a sort order required?
body: <p>We discovered that in our .NET 4.51/nHibernate/SQL 2008 app that when we do a single large query, we get all the data, but when we page against certain large results sets, rows are missed.</p>
  
  <p>(In our testing, when we do pages of 200 per page, against a result set that is 1000 or more rows, we see the problem.)</p>
  
  <p>We tried the app using the SQL2000 and SQL2005 nHibernate dialects, and saw no difference in behavior. (The query syntax does evolves to use newer constructs in SQL2005, but the same data is missing when using SQL2005 dialect.)</p>
  
  <p>nHibernate generates SQL queries.
  We are doing paging.
  If we page by 100, the way nHibernate generates the successive SQL queries is:</p>
  
  <ul>
  <li>TOP 100  // gives us first 100</li>
  <li>TOP 200  // gives us 2nd 100 of this block</li>
  <li>etc</li>
  </ul>
  
  <p>The above, without an ORDER BY / sorting at nHibernate level, has the end result that some rows never surface to nHibernate. We surmise this is due arbitrary sorting by SQL, so that rows are "moving around" within the pages (and thus "hide" from our application code).</p>
  
  <p>If we do the nHibnerate query as a single shot (returning all rows), we see all the data. (This query below is generated by nhibernate.)</p>
  
  <p>Will adding an order by clause (aka nHibernate sorting) help?</p>
  
  <pre><code>SELECT top 33 
   ... &lt;field list&gt; 
  FROM 
    salesOrder this_ left outer join [Item] Item2_ on this_.ItemId=Item2_.ItemId 
  WHERE this_.AccountId = @p0 
  and this_.ModifiedAt &gt; @p1 
  and this_.ModifiedAt &lt;= @p2
  </code></pre>
  
  <p>The database is <strong>quite large</strong>, but is not fragmented, and it performance is great. The problem has only been seen on very large tables (100 million rows) and even then only when the query returns > 1000 rows AND is paged.</p>
  
  <p>Is an Order By clause (a sort at the nHibernate level) the solution, or is there another path we need to take?</p>