Sample Header Ad - 728x90

Paging in SQL Server: Is an order by clause required? (currently, rows are being missed)

4 votes
1 answer
3175 views
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 ... 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
Asked by Jonesome Reinstate Monica (3489 rep)
Oct 29, 2014, 01:31 PM
Last activity: Oct 29, 2014, 09:48 PM