1

Firstly, I apologize - I couldn't create an example SQL Fiddle, because the site doesn't seem to be working right now.

Now, onto the problem... I'm getting an inconsistent sort order on SQL Server 2014 when using OFFSET..FETCH on a query sorted over a non-unique NVARCHAR field.

The ORDER BY looks like this:

ORDER BY CASE WHEN [Field] IS NULL THEN 1 ELSE 0 END ASC, [Field] DESC OFFSET 100 ROWS FETCH NEXT 3 ROWS ONLY

Every execution of the statement yields a slightly different combination of the 3 rows. Technically, all results are correctly sorted, but the returned row IDs are not always the same.

The field in question has an index on it, but I've determined that the execution plan doesn't seem to be using it.

Testing showed that the culprit is the CASE WHEN expression. The reason I'm using it is to ensure that NULL values are reliably sorted before (when ASC) or after (when DESC) any other value across all the different DBs that my app works with (aside from SQL Server there's also Oracle, DB2, PostgreSQL, Firebird, and SQLite).

I'm considering removing this rule when generating statements for SQL Server, since it obviously forgoes the index and therefore doesn't yield reliable results. But before I do, I want to ask: is there any other practical solution to keep the sort order stable? Optimally, the solution should just use the index which is there for that very purpose.

  • 4
    What index is used is in itself irrelevant to the results (except for the speed with which they're returned). If the column(s) that you order by aren't unique, the order within those non-unique rows is unspecified. You are not guaranteed a consistent sort order in that case even if the query happens to hit an index -- that's just getting lucky. Add the ID (assuming that's the clustered index) as a tiebreaker. (This isn't a full answer; the other part of your question is how to ensure the index is used.) – Jeroen Mostert Sep 18 '18 at 12:52
  • 2
    Please provide more details - table schema, any other indexes, and enough sample data to reproduce the problem. But I can tell you that if more than three rows have the same value for Field, you need to provide more information to break ties and dictate order. – Aaron Bertrand Sep 18 '18 at 12:52
  • @Jeroen: I understand now. I was incorrectly assuming that the use of index guarantees a stable order. That was the reason I asked how to ensure that the index is used. I will try adding a tiebreaker, as you suggest. – aoven Sep 18 '18 at 12:59
  • @Jeroen: Tiebreaker suggestion seems to be helping! Please, post an answer so I can accept it! – aoven Sep 18 '18 at 14:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.