Data is not ordered correctly with pagination - Sql Server 2012
-
Hi everyone, I hope to have chosen the right forum section for my question.
I have a query (coming from Hibernate) that uses the ROW_NUMBER() function to number the resulting rows according to a column (a string) and then select part of this rows. The query is this:
WITH query AS (select ROW_NUMBER() OVER (order by tab1.numero asc) as __hibernate_row_nr__,tab1.id, tab1.numero,
( select tab2.id from table_2 tab2
where tab2.fk_table_1 = tab1.id) as formula
from table_1 tab1)
SELECT * FROM query WHERE __hibernate_row_nr__ BETWEEN 0 AND 50
When I execute the query with the subselect formula the result is not ordered:
When I execute the query without the subselect formula the result is ordered.
Why this?
The strangest thing is that I have this problem only on a particular schema of a customer, on all the other schemas the result in ordered with or without formula. I tried to compared the data of the different schemas but everything seems normal.
Do you have any ideas?
-
You need an ORDER BY on the outer query. Without said ORDER BY, the optimiser is free to choose plans that return the data in any order at all, as without said ORDER BY there is no guarantee of order of data. Hence, as written, the data will be in whatever order the last query operator left it in.
WITH query AS (select ROW_NUMBER() OVER (order by tab1.numero asc) as __hibernate_row_nr__,
tab1.id, tab1.numero,
( select tab2.id from table_2 tab2
where tab2.fk_table_1 = tab1.id) as formula
from table_1 tab1)
SELECT __hibernate_row_nr__, id, numero, formula FROM query WHERE __hibernate_row_nr__ BETWEEN 0 AND 50
ORDER BY __hibernate_row_nr__;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (11/16/2016)
You need an ORDER BY on the outer query. Without said ORDER BY, the optimiser is free to choose plans that return the data in any order at all, as without said ORDER BY there is no guarantee of order of data. Hence, as written, the data will be in whatever order the last query operator left it in.WITH query AS (select ROW_NUMBER() OVER (order by tab1.numero asc) as __hibernate_row_nr__,
tab1.id, tab1.numero,
( select tab2.id from table_2 tab2
where tab2.fk_table_1 = tab1.id) as formula
from table_1 tab1)
SELECT __hibernate_row_nr__, id, numero, formula FROM query WHERE __hibernate_row_nr__ BETWEEN 0 AND 50
ORDER BY __hibernate_row_nr__;
Thank you very much for your fast reply GilaMonster!
Yes, adding ORDER BY solves my problem, but Hibernate (at least using 3.6.10 with SqlServer2008Dialect) won't translate it in that way. It will just output that query without the order by.
Still I'm curious why the problem arises only with a schema and disappear if I exclude the subselect...
-
francesco.giusto.sb (11/16/2016)
Yes, adding ORDER BY solves my problem, but Hibernate (at least using 3.6.10 with SqlServer2008Dialect) won't translate it in that way. It will just output that query without the order by.You may need to switch to a stored procedure then, or sort on the client, because without an ORDER BY, you are not going to get the data ordered as you expect every time
Still I'm curious why the problem arises only ...
It's not a problem. It's expected behaviour.
Without an ORDER BY, SQL is free to return the data in any order at all. Without the subquery the plan generated for the query is one that leaves the data in a particular order at the end of the query. With the subquery the the plan generated for the query is one that leaves the data in a different order at the end. This is not a problem, since no specific order has been specified (via ORDER BY) any order is valid.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Ok, I think I will have to order on the client then.
Thanks GilaMonster 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply