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

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

Using TOP(n) without ORDER BY is going to return you n rows in whatever way Sql Server cares. As per the documentation:


If the query has no ORDER BY clause, the order of the rows is arbitrary.

In addition, there is no WITH TIES. Consider using it too, lest you might miss some rows too:


WITH TIES Specifies that additional rows be returned from the base
result set with the same value in the ORDER BY columns appearing as
the last of the TOP n (PERCENT) rows. TOP...WITH TIES can be specified
only in SELECT statements, and only if an ORDER BY clause is
specified.


The returned order of tying records is arbitrary. ORDER BY does not
affect this rule.

Consider update to Sql Server 2012/2014. You could enjoy new features like automatic paging with OFFSET FETCH.

StackExchange Database Administrators Q#81382, answer score: 9