Yep, that was the reason why I wrote there's a small semantic change
in the dialect.
I added that because the old way did not work for the new syntax.
Adding an ORDER BY 1 to the query leads to sorting by the first result
expression.
In the case with DISTINCT it is not possible to sort by some arbitrary
expression without modification to the SELECT list thus needing better
SQL parsing because all sort order expressions must be part of the
SELECT list in this case. The good thing about the new paging
implementation suggested above is that it's just not required to do
remarkable parsing and manipulations on the SQL string.
And I think that with the SQL generated from current SQL Server
Dialects, the DISTINCT leads to problems because the ROW_NUMBER()
function used there makes each pair of rows distinct.
So the following select that does something like those dialects leads
to duplicate type rows:
with q as (select distinct row_number() over (order by
current_timestamp) rownum, type from sys.tables)
select top 10 * from q order by rownum
So even if there are reasons for not doing this ORDER BY 1 thing or
the better SQL parsing discussed in another thread is available so an
intrusive paging implementation is not a problem any more, I would
suggest implementing a paging based on new "Denali" syntax because
then it is easy to make it obvious in the SQL string that there is no
sorting at all:
select distinct type from sys.tables
could then be converted for paging to
with q as (select distinct type, 1 as __nohibernatesorting__)
select * from q order by __nohibernatesorting__ offset 0 fetch first
10 rows only
This one avoids the row_number problem described above.
On 10 Jan., 03:05, Gerke Geurts <[email protected]> wrote:
> The dialect for SQL Server 2005 currently deals with the absence of an
> ORDER BY statement by ordering on CURRENT_TIMESTAMP when an order is
> required to generate valid SQL for a paging query.
