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 t开发者_开发百科he 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?

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]

Nhibernate is smart enough to optimize query. If you select first 10 rows it will use TOP statement. If you select not first rows then it will use RowNum.

In sql 2000 there is no RowNum function, that's why it is impossible with usual query to select required number of rows. For sql 2000 as I know for such an optimization views were used.

In sql 2005/2008 query will select only required rows.