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.

Reply via email to