Is Queryable.OrderBy unstable for SQL Server database?

Question

OrderBy is stable for LINQ to Objects, but MSDN on Queryable.OrderBy doesn't mention if it is stable or not.

I guess it depends on the provider implementation. Is it unstable for SQL Server? Because it looks so. I did a quick look at Queryable source code, but it is not obvious from there.

I need to order a collection before other operations and I want to use IQueryable, rather than IEnumerable for the sake of performance.

// All the timestamps are the same and I am getting inconsistent 
// results by running it multiple times, first few pages return the same results
var result = data.OrderBy(i => i.TimeStamp).Skip(start).Take(length);

but if I use

var result = data.ToList().OrderBy(i => i.TimeStamp).Skip(start).Take(length);

It works just fine, but I lose performance boost from LINQ to SQL. It seems combination of Queryable OrderBy/Skip/Take produce inconsistent results.

SQL Code generated seems fine to me:

SELECT 
...
FROM [dbo].[Table] AS [Extent1]
ORDER BY [Extent1].[TimeStamp] ASC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

OrderBy is translated to SQL order by so it depends on the db provider. — arekzyla, Apr 19 '18 at 18:10
Did you check what SQL query gets executed? See https://stackoverflow.com/questions/18237312/get-sql-query-from-linq-to-sql — MarcinJuraszek, Apr 19 '18 at 18:15

arekzyla · Accepted Answer · 2018-04-19T19:47:56.180

In Linq-to-Entities LINQ queries are translated into SQL queries so Linq-to-Objects implementation of OrderBy doesn't matter. You should look at your database implementation of ORDER BY. If you are using MS SQL you can find in docs that:

To achieve stable results between query requests using OFFSET and FETCH, the following conditions must be met: (...)

The ORDER BY clause contains a column or combination of columns that are guaranteed to be unique.

So ORDER BY for the same values does not guarantee the same order so limiting it could provide different results set. To solve this you can simply sort by some additional column that has unique values e.g. id. So basically you will have:

var result = data
    .OrderBy(i => i.TimeStamp)
    .ThenBy(i => i.Id)
    .Skip(start)
    .Take(length);

nice answer! `ThenBy` solves the problem. And thanks for dropping lines from MS SQL docs, good to know that. — Igor Yalovoy, Apr 19 '18 at 19:21

score 0 · Answer 2 · answered Apr 19 '18 at 18:35

I take it that by "stable", you mean consistent. If you didn't have the ORDER BY in a SQL query, the order of the data is not guaranteed for each time you run the query. It will simply return all of the data in whatever order is most efficient for the server. When you add the ORDER BY, it will sort that data. Since you are sorting data where all of the sort values are the same, no rows are being reordered, so the ordered data is in an order you don't expect. If you need a specific order, you will need to add a secondary sort column such as an ID.

It is a best to never assume the order of data coming back from the server unless you explicitly define what that order is.

Is Queryable.OrderBy unstable for SQL Server database?

2 Answers2