Sql – NHibernate unexpected ORDER BY Statement

nhibernatesqlsql-order-by

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'

Best Answer

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?