NHibernate paging with SQL Server

When using SetFirstResult(start) and SetMaxResults(count) methods to implement paging I've noticed that the generated query only does a select top count * from some_table and it does not take the start parameter into account or at least not at the database level. It seems that if I instruct NHibernate to execute the following query:

var users = session.CreateCriteria<User>()
                   .SetFirstResult(100)
                   .SetMaxResults(5)
                   .List<User>();

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.

I've verified that with an SQLite database NHibernate takes advantage of the OFFSET and LIMIT keywords to filter results at the database level. I am aware that there's no equivalent of the OFFSET keyword and Oracle's ROWNUM in SQL Server 2000 but is there any workaround? How about SQL Server 2005/2008?

Solution

T-SQL, the variant of the SQL language which Microsoft SQL Server uses, does not have a limit clause. It has a select top {...} modifier which you see NHibernate taking advantage of with SQL Server 2000.

With SQL Server 2005, Microsoft introduced the Row_Number() over (order by {...}) function which can be used as a replacement to the limit clause, and you can see NHibernate taking advantage of that with SQL Server 2005/2008.

A query for SQLite might look like

select c.[ID], c.[Name]
from [Codes] c
where c.[Key] = 'abcdef'
order by c.[Order]
limit 20 offset 40

while a similar query for SQL Server 2005 might look like

select c.[ID], c.[Name]
from (
    select c.[ID], c.[Name], c.[Order]
        , [!RowNum] = Row_Number() over (order by c.[Order])
    from [Codes] c
    where c.[Key] = 'abcdef'
) c
where c.[!RowNum] > 40 and c.[!RowNum] <= 60
order by c.[Order]

or, using Common Table Expressions, it might look like

with
    [Source] as (
        select c.[ID], c.[Name], c.[Order]
            , [!RowNum] = Row_Number() over (order by c.[Order])
        from [Codes] c
        where c.[Key] = 'abcdef'
    )
select c.[ID], c.[Name]
from [Source] c
where c.[!RowNum] > 40 and c.[!RowNum] <= 60
order by c.[Order]

There is a way to do it in SQL Server 2000 as well

select c.[ID], c.[Name]
from (
    select top 20 c.[ID], c.[Name], c.[Order]
    from (
        select top 60 c.[ID], c.[Name], c.[Order]
        from [Codes] c
        where c.[Key] = 'abcdef'
        order by c.[Order]
    ) c
    order by c.[Order] desc
) c
order by c.[Order]