We have some code that pages through a SQL result. (Currently running on SQL Server 2008)
We notice that when paging is done, some rows are not returned. Let me clarify this a bit:
nHibernate generates SQL queries.
We are doing paging.
If we page by 100, the way nHibernate generates the successive SQL queries is:
- TOP 100 // gives us first 100
- TOP 200 // gives us 2nd 100 of this block
- etc
The above, without an ORDER BY / sorting at nHibernate level, has the end result that some rows never surface to nHibernate. We surmise this is due arbitrary sorting by SQL, so that rows are "moving around" within the pages (and thus "hide" from our application code).
If we do the nHibnerate query as a single shot (returning all rows), we see all the data. (This query below is generated by nhibernate.)
Will adding an order by clause (aka nHibernate sorting) help?
SELECT top 33
... <field list>
FROM
salesOrder this_ left outer join [Item] Item2_ on this_.ItemId=Item2_.ItemId
WHERE this_.AccountId = @p0
and this_.ModifiedAt > @p1
and this_.ModifiedAt <= @p2
Best Answer
Using
TOP(n)
withoutORDER BY
is going to return you n rows in whatever way Sql Server cares. As per the documentation:In addition, there is no
WITH TIES
. Consider using it too, lest you might miss some rows too:Consider update to Sql Server 2012/2014. You could enjoy new features like automatic paging with
OFFSET FETCH
.