Подкачка страниц NHibernate с SQL Server
Эта альтернатива работает, где xargs не является availabe:
grep -L -r 'Subject: \[SPAM\]' . | while read f; do mv "$f" out; done
2 ответа
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.