NHibernate unexpected ORDER BY Statement

Question

Hi, I am using NHibernate 2.0, and when I submit a request asking for the top 2 records to be returned, I get a number of ORDER BY clauses in my SQL. When I take out the Max results, the query looks fine (no ORDER BY statements). Why is NHibernate automatically adding this when I am looking for a subset of records? Thanks in advance

See SQL statements below:

Unexpected ORDER BY

exec sp_executesql 
N'SELECT TOP 2 Person1_36_0_, LastReco2_36_0_, SSN3_36_0_,
 FirstName4_36_0_, LastName5_36_0_, MiddleIn6_36_0_, Title7_36_0_, Suffix8_36_0_, 
DateOfBi9_36_0_, IsDeceased10_36_0_, Decease11_36_0_, Contact12_36_0_, MailHol13_36_0_, 
MailHol14_36_0_, MailHol15_36_0_, Preferr16_36_0_, CreatedBy17_36_0_, Created18_36_0_, 
ModifiedBy19_36_0_, Modifie20_36_0_ 
FROM (SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_0__) as row, 
query.Person1_36_0_, query.LastReco2_36_0_, query.SSN3_36_0_, query.FirstName4_36_0_, 
query.LastName5_36_0_, query.MiddleIn6_36_0_, query.Title7_36_0_, query.Suffix8_36_0_, 
query.DateOfBi9_36_0_, query.IsDeceased10_36_0_, query.Decease11_36_0_, 
query.Contact12_36_0_, query.MailHol13_36_0_, query.MailHol14_36_0_, query.MailHol15_36_0_, 
query.Preferr16_36_0_, query.CreatedBy17_36_0_, query.Created18_36_0_, 
query.ModifiedBy19_36_0_, query.Modifie20_36_0_, query.__hibernate_sort_expr_0__
FROM 
(SELECT this_.Person_id as Person1_36_0_, this_.[LastRecordVersion] as LastReco2_36_0_, 
this_.[SSN] as SSN3_36_0_, this_.[FirstName] as FirstName4_36_0_, this_.[LastName] as 
LastName5_36_0_, this_.[MiddleInitial] as MiddleIn6_36_0_, this_.[Title] as Title7_36_0_, 
this_.[Suffix] as Suffix8_36_0_, this_.[DateOfBirth] as DateOfBi9_36_0_, this_.[IsDeceased] 
as IsDeceased10_36_0_, this_.[DeceasedDate] as Decease11_36_0_, this_.[ContactMethod_id] as 
Contact12_36_0_, this_.[MailHoldReason_id] as MailHol13_36_0_, this_.[MailHoldStartDate] as 
MailHol14_36_0_, this_.[MailHoldEndDate] as MailHol15_36_0_, this_.[PreferredName] as 
Preferr16_36_0_, this_.[CreatedBy] as CreatedBy17_36_0_, this_.[CreatedDate] as 
Created18_36_0_, this_.[ModifiedBy] as ModifiedBy19_36_0_, this_.[ModifiedDate] as 
Modifie20_36_0_, CURRENT_TIMESTAMP as __hibernate_sort_expr_0__ 
FROM MC_Person this_ 
WHERE this_.[SSN] = @p0) query) page 
WHERE **page.row > 0 ORDER BY __hibernate_sort_expr_0__**',
N'@p0 nvarchar(9)',@p0=N'123456789'

Correct SQL (without getting Top 2 records)

exec sp_executesql 
N'SELECT this_.Person_id as Person1_36_0_, this_.[LastRecordVersion] as LastReco2_36_0_, 
this_.[SSN] as SSN3_36_0_, this_.[FirstName] as FirstName4_36_0_, this_.[LastName] as 
LastName5_36_0_, this_.[MiddleInitial] as MiddleIn6_36_0_, this_.[Title] as Title7_36_0_, 
this_.[Suffix] as Suffix8_36_0_, this_.[DateOfBirth] as DateOfBi9_36_0_, this_.[IsDeceased] 
as IsDeceased10_36_0_, this_.[DeceasedDate] as Decease11_36_0_, this_.[ContactMethod_id] as 
Contact12_36_0_, this_.[MailHoldReason_id] as MailHol13_36_0_, this_.[MailHoldStartDate] as 
MailHol14_36_0_, this_.[MailHoldEndDate] as MailHol15_36_0_, this_.[PreferredName] as 
Preferr16_36_0_, this_.[CreatedBy] as CreatedBy17_36_0_, this_.[CreatedDate] as 
Created18_36_0_, this_.[ModifiedBy] as ModifiedBy19_36_0_, this_.[ModifiedDate] as 
Modifie20_36_0_
FROM MC_Person this_ 
WHERE this_.[SSN] = @p0',
N'@p0 nvarchar(9)',@p0=N'123456789'

Answer 1

It's the way that paging is implemented. So there's no special case for just taking the top n elements, because for example Oracle does not support this construct.

So anything that has either of the paging limits set is done this way.

Are you getting the correct results?

Answer 2

A top selection doesn't make much sense without ordering, since ordering might change by maintainance of the databasetable.

Don't you somehow, define which sort of top two results you want? hence the order by?