OFFSET ... FETCH overlapping results on second page

When using OFFSET ... FETCH, I'm having I'm getting...interesting results.

Here are two sqlfiddles to illustrate my problem.

http://sqlfiddle.com/#!6/71ac1/4 and http://sqlfiddle.com/#!6/71ac1/8

The first fiddle is the first page, while the second is the...second page.

Here are the unpaginated results.

First thing, the paginated results are not in the same order as the unpaginated results. I'm cool with that, I guess, as I'm not showing the unpaginated results anyway.

However, event though I'm specified an offset of 0, and for some reason it decided to put them in the reverse order they were entered, it skips the first 3.

On the second page, I see see results that appear on the first page.

The fact that they all have the same value, although in this example is contrived, is a reality in the specific query I'm dealing with in real life. It's a table, and the user selected to sort by date.

This is not unexpected. Your ORDER BY does not determine a complete (unique) order. As described in MSDN documentation:

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

-
The underlying data that is used by the query must not change. ...

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

You have order by date but all the rows have the same value in date. To have determinate results with TOP / OFFSET ... FETCH, you have to use an ORDER BY that completely determines an ordering.

You could use for example:

order by date asc,
         name asc

or (if (date, name) is not unique either:

order by date asc,
         eventID asc

or:

order by date asc,
         name asc,
         eventID asc

If the query is produced by some form and can be customized by the user, you could - behind the scenes - add the primary key column(s) (I guesseventID here) in the end of the order by list, so you have an order that is determinate and stays the same across different executions of the query.

StackExchange Database Administrators Q#138206, answer score: 13