4

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
3
  • What do you mean "some rows are not returned"? And why do you have TOP without ORDER BY? And "when paging is done"? Your query does not show any paging. Oct 29, 2014 at 13:42
  • @ypercube OP enhanced. We have no order by because we did not think it needed (and when getting all rows, order by is not needed). Is order by required when doing paging? Oct 29, 2014 at 13:46
  • 8
    TOP without ORDER 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. Oct 29, 2014 at 13:47

1 Answer 1

9

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.