title: NHibernate paging with SQL Server
body: <p>When using <code>SetFirstResult(start)</code> and <code>SetMaxResults(count)</code> methods to implement paging I've noticed that the generated query only does a <code>select top count * from some_table</code> and it does not take the <code>start</code> parameter into account or at least not at the database level. It seems that if I instruct NHibernate to execute the following query:</p>
  
  <pre><code>var users = session.CreateCriteria&lt;User&gt;()
                     .SetFirstResult(100)
                     .SetMaxResults(5)
                     .List&lt;User&gt;();
  </code></pre>
  
  <p>105 records will transit between the database server and the application which will take care to strip the first 100 records. With tables containing many rows this could be a problem.</p>
  
  <p>I've verified that with an <a href="http://www.sqlite.org/" rel="noreferrer">SQLite</a> database NHibernate takes advantage of the <code>OFFSET</code> and <code>LIMIT</code> keywords to filter results at the database level. I am aware that there's no equivalent of the <code>OFFSET</code> keyword and Oracle's <code>ROWNUM</code> in SQL Server 2000 but is there any workaround? How about SQL Server 2005/2008?</p>