title: Hibernate (/JPA) server-side paging and MS SQL Server
body: <p>I implementing a <strong>server-side data paging</strong> with <strong>hibernate</strong> / JPA, the underlying database is <strong>MS SQL Server</strong> 2008.</p>
  
  <p>The SQL is generated like this:</p>
  
  <pre><code>criteria.setFirstResult(pagingParams.getDisplayStart())
  .setMaxResults(pagingParams.getDisplayLength());
  </code></pre>
  
  <p>(The main work lays in creating the appropriate filter / sorting, but that's not relevant here)</p>
  
  <p>What I'm observing is the following SQL:</p>
  
  <pre><code>page (0-20):
  select top 20 this_.id as id11_9_,...
  
  page (20-40):
  select top 40 this_.id as id11_9_,... 
  
  page (40-60):
  select top 60 this_.id as id11_9_,... 
  </code></pre>
  
  <p>... and so on. </p>
  
  <p>Obviously this (a) will run into serious issues if the underlying resultset gets too big and (b) hasn't much to do with paging at all :-(</p>
  
  <p>Anyone who had the same issue? </p>
  
  <p><strong>Update</strong>: It seems as if NHibernate (the .NET implementation of Hibernate) <a href="https://stackoverflow.com/questions/1651723/nhibernate-paging-with-sql-server">takes advantage</a> of the <code>Row_Number()</code> function of T-SQL. Pity that Hibernate doesn't... </p>