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
TOP
withoutORDER BY
? And "when paging is done"? Your query does not show any paging.TOP
withoutORDER BY
will give you indeterminate results (possibly different results per execution.) I don't know how you do the paging but I can't think of a way that does not involve ordering or some kind.