Hi, Something strange is going on with a paging implementation... We're using SQL Server 2008 (dialect: MsSql2008Dialect)
We're using paging with SetFirstResult and SetMaxResults and it's working like it should. When we're adding an order things get weird. >From what I understand to so far is that SetFirstResult(0) is doing *nothing extra* to the generated query. The Order criteria is added at the end of the query, like a normal order clause. With a number larger than 0 the query gets fundamentally different. I tried to trick the system, by feeding -1 but that didn't help ;-) Another 'hack' I tried is to add an SQL Expression where the ORDER clause is added by hand, but it automatically get's translated in a query like the 2nd *SetFirstResult(0) generates:* SELECT top 25 this_.VerwerkingsVerslagSamenvattingId as Verwerki1_140_0_, this_.ServicecenterCode as Servicec2_140_0_, this_.Bedrijfsnummer as AsapBedr3_140_0_, this_.InterfaceNaam as Interfac4_140_0_, this_.Bestandsnaam as Bestands5_140_0_, this_.TotaalAantalRegels as TotaalAa6_140_0_, this_.TotaalAantalGoed as TotaalAa7_140_0_, this_.TotaalAantalFout as TotaalAa8_140_0_, this_.TotaalAantalWaarschuwingen as TotaalAa9_140_0_, this_.Startdatum as Startdatum140_0_, this_.Einddatum as Einddatum140_0_ FROM [Database].[dbo].[VerwerkingsVerslagSamenvatting] this_ WHERE this_.ServicecenterCode = @p1 ORDER BY ServicecenterCode ASC; ;@p0 = 'SSC', @p1 = 'SSC' *SetFirstResult(25) generates:* SELECT TOP 25 Verwerki1_140_0_, Servicec2_140_0_, AsapBedr3_140_0_, Interfac4_140_0_, Bestands5_140_0_, TotaalAa6_140_0_, TotaalAa7_140_0_, TotaalAa8_140_0_, TotaalAa9_140_0_, Startdatum140_0_, Einddatum140_0_ FROM (SELECT this_.VerwerkingsVerslagSamenvattingId as Verwerki1_140_0_, this_.ServicecenterCode as Servicec2_140_0_, this_.AsapBedrijfsnummer as AsapBedr3_140_0_, this_.InterfaceNaam as Interfac4_140_0_, this_.Bestandsnaam as Bestands5_140_0_, this_.TotaalAantalRegels as TotaalAa6_140_0_, this_.TotaalAantalGoed as TotaalAa7_140_0_, this_.TotaalAantalFout as TotaalAa8_140_0_, this_.TotaalAantalWaarschuwingen as TotaalAa9_140_0_, this_.Startdatum as Startdatum140_0_, this_.Einddatum as Einddatum140_0_, ROW_NUMBER() OVER(ORDER BY ServicecenterCode) as __hibernate_sort_row FROM [Raet.Midoffice.Database].[dbo].[VerwerkingsVerslagSamenvatting] this_ WHERE this_.ServicecenterCode = @p1) as query WHERE query.__hibernate_sort_row > 25 ORDER BY query.__hibernate_sort_row;;@p0 = 'SSC', @p1 = 'SSC' The second query gives other results than the first query and I actually don't know why. I assume that SQL Server applies another order strategy with the ROW_NUMBER function. Is there any workaround known so the order clause is exactly the same for every query? My impression is that the newer NHibernate versions are handling this correct. We're in the middle of upgrading several components (including NHibernate) but this is something that cannot wait for the upgrade. Any hints will be appreciated! Regards Erik -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/nhusers. For more options, visit https://groups.google.com/d/optout.
