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.