SQL query engine is prepared to return correct results as soon as possible. But correctness means being correct from mathematical perspective. Problem that I will describe is obvious, but it is not what you sometimes expect from SQL engine. Let’s imagine a simple SQL table Contact with 3 columns:

  • ID
  • FirstName
  • LastName

Now imagine that there are millions of records and business owner decided to display 10 records per page. User decided to sort results ascending by FirstName. In case of ORACLE database, NHibernate will generate a query like this for the first page:

SELECT ID, FirstName, LastName
FROM
  (SELECT ID,FirstName, LastName
  FROM Contact
  ORDER BY FirstName ASC) _t0
WHERE rownum <= :p0
;:p0=10

For the next pages something like this:

SELECT ID, FirstName, LastName
FROM
(
  ( SELECT ID,FirstName, LastName, rownum as _r
    FROM Contact
    ORDER BY FirstName ASC
  ) _t0
  WHERE rownum <= :p0
) 
WHERE _r > :p1
;:p0=20,:p1=10

The problem is that both queries have different execution plans so sometimes the first page can return exactly the same record(s) as the second page. It means that you will have no access to some Contact record(s) while sorting e.g. by FirstName, because in the first plan the specific record is on second page. In the second plan the record is on the first page. If you use only

 ORDER BY FirstName ASC

it happens, because from mathematical point of view records:

  • ID = 43
  • FirstName = John
  • LastName = Smith

and

  • ID = 111
  • FirstName = John
  • LastName = Doe

are equal while sorting by FirstName. SQL engine goal is to return records quickly. Because John = John, engine does not order both records until it is required. So e.g. record ID=43 can be 10th on the first page and 1st on the second page. It is a correct behavior! A simple trick

 ORDER BY FirstName ASC, ID ASC

and now you are forcing the database to additionally sort by clustered index (unique key). You are guarantied the uniqueness of the sorted records. Now both execution plans have to apply an extra sort order. Result? Your application is bug free for paging mechanism 😉 .