This is the syntax of SqlCe4 SELECT * FROM Customers ORDER BY [Customer ID] OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY
How much we have to pay to have that syntax in SQL-SERVER ? On Thu, Sep 2, 2010 at 2:24 PM, Fabio Maulo <[email protected]> wrote: > ah Joseph... > please don't forget to ask to your DBA if he sent the issue to the MsSQL > team about the pagination syntax used by MsSQL. > > > On Thu, Sep 2, 2010 at 2:22 PM, Fabio Maulo <[email protected]> wrote: > >> When you find something so complicated to query perhaps the wrong thing is >> not in the SQL. >> btw, you can use your SP with NH and have your nice OO result. >> >> >> On Thu, Sep 2, 2010 at 2:16 PM, Joseph Daigle <[email protected]>wrote: >> >>> And the crazy thing about this particular pagination example: >>> >>> We have a WHERE clause on the "inner select" which actually generates >>> the page, and then we need a WHERE clause on that resulting page (this >>> is a complicated use case involving getting back altered rows on a >>> visible page). So the common table expression in the latter example is >>> the cleaner, and possibly better performing, query. But we couldn't >>> ever get NHibernate to generate that. >>> >>> I'd replace it with a stored procedure, except that the WHERE clause >>> is not always the same. There are maybe 50-100 distinct query plans >>> that are generated based on combination of application specified >>> filtering and sorting. The former query, as generated by NHibernate, >>> performs well enough as long as indexes are tuned. >>> >>> -- >>> Joseph Daigle >>> >>> On Sep 2, 1:06 pm, Fabio Maulo <[email protected]> wrote: >>> > aaaaaaaahhhh pagination.... nice example!! >>> > Ask to your DBA if he sent an issue to MsSQL team asking for a >>> > human-oriented-syntax for pagination stuff. >>> > I mean something similar to ORACLE, Firebird, MySQL, Postgre etc. etc. >>> or >>> > even something like SqlCe4... >>> > We can accept even something like this: >>> > SELECT SKIP 100 TAKE 25 ........... FROM ...... WHERE ..... ORDER BY >>> ...... >>> > >>> > On Thu, Sep 2, 2010 at 1:59 PM, Joseph Daigle <[email protected] >>> >wrote: >>> > >>> > >>> > >>> > >>> > >>> > > Hi Frans, >>> > >>> > > > care to share the name of this community member? :) >>> > >>> > > Send me an e-mail and I'll share it offline (I don't want to burn >>> > > anyone unnecessarily). >>> > >>> > > > care to share one as an example? I.o.w. I think it's more >>> of an >>> > > edge >>> > > > case when you run into these limitations, with any o/r mapper btw. >>> Often >>> > > > these queries are using temp tables, cursors etc. >>> > >>> > > They are edge cases. And as I said, we can drop down to stored >>> > > procedures when necessary to resolve these edge cases. >>> > >>> > > > what does 'not stable' mean, exactly? Correlated subqueries >>> are >>> > > > either correct, or not, but 'unstable' is IMHO a term not used in >>> > > > combination of correlated subqueries. >>> > >>> > > "not stable" is a term the consultant used, which I believe means >>> that >>> > > the sql statement performance will not scale linearly as the load >>> > > increases and/or table size increases >>> > >>> > > > An example would be great. >>> > >>> > > The example used: >>> > >>> > > SELECT ... >>> > > FROM ... >>> > > WHERE [Id] IN ( >>> > > SELECT TOP 200 >>> > > [Id] >>> > > FROM ... >>> > > WHERE ... >>> > > ORDER BY ... >>> > > ) >>> > > WHERE ... >>> > >>> > > versus >>> > >>> > > SELECT ... >>> > > FROM ( >>> > > SELECT ..., >>> > > ROW_COUNT() OVER(ORDER BY...) AS [row_num] >>> > > FROM ... >>> > > WHERE ... >>> > > ) >>> > > WHERE ... >>> > > AND [row_num] < 200 >>> > >>> > > The former is what we can generate with NHibernate using Criteria. >>> The >>> > > second we cannot. >>> > >>> > > Thanks, >>> > >>> > > -- >>> > > Joseph Daigle >>> > >>> > > -- >>> > > You received this message because you are subscribed to the Google >>> Groups >>> > > "nhusers" group. >>> > > To post to this group, send email to [email protected]. >>> > > To unsubscribe from this group, send email to >>> > > [email protected]<nhusers%[email protected]> >>> <nhusers%[email protected]<nhusers%[email protected]>> >>> > > . >>> > > For more options, visit this group at >>> > >http://groups.google.com/group/nhusers?hl=en. >>> > >>> > -- >>> > Fabio Maulo >>> >>> -- >>> You received this message because you are subscribed to the Google Groups >>> "nhusers" group. >>> To post to this group, send email to [email protected]. >>> To unsubscribe from this group, send email to >>> [email protected]<nhusers%[email protected]> >>> . >>> For more options, visit this group at >>> http://groups.google.com/group/nhusers?hl=en. >>> >>> >> >> >> -- >> Fabio Maulo >> >> > > > -- > Fabio Maulo > > -- Fabio Maulo -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
