In Sql Server 2014 ORDER BY clause with OFFSET FETCH NEXT returns weird results

Question

I am currently using Sql Server 2014 Professional and the current version is (12.0.4100). I have a View and I am trying to SELECT 10 rows with specific offset.
My View is like below:

    BeginTime   |     EndTime    | Duration |   Name   
09:00:00.0000000|16:00:00.0000000|    1     | some_name1
09:00:00.0000000|16:00:00.0000000|    2     | some_name2
09:00:00.0000000|16:00:00.0000000|    3     | some_name3
09:00:00.0000000|16:00:00.0000000|    4     | some_name4
09:00:00.0000000|16:00:00.0000000|    5     | some_name5
09:00:00.0000000|16:00:00.0000000|    6     | some_name6
09:00:00.0000000|16:00:00.0000000|    7     | some_name7

there are 100 rows like these and all have the exact same value in BeginTime and EndTime. Duration is incremented from 1 to 100 in related table.
If query is only:

SELECT * FROM View_Name

ResultSet is correct. I can understand it by checking the duration column.

If I want to fetch only 10 rows starting from 0, ResultSet is correct and it is correct for starting from up to 18. When I want to fetch 10 rows starting from 19 or more than 19, Duration in ResultSet returns irrelevant results like Duration reversed. But it never returns the rows which has duration more than 11. The query that I used to fetch specific rows is as follows:

SELECT * FROM View_Name ORDER BY BeginTime ASC OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

There is also something strange in this situation; if I specify USE master, this problem disappears, but, if I specify USE [mydb_name], the problem appears again.
By the way, I am using SQL SERVER 2014 Professional v(12.0.2269) in my local pc, this problem disappears for the above situation.

PS: I can not use USE master because, I am creating and listing the view dynamically, in Stored Procedures.
Any help, answer or comment will be accepted.
Thank You!

Answer 1

The documentation explains:

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

. . .

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

What happens in your case is that BeginTime is not unique. Databases in general -- and SQL Server in particular -- do not implement stable sorts. A stable sort is one where the rows are in the same order when the keys are the same. This is rather obvious, because tables and result sets represent unordered sets. They have no inherent ordering.

So, you need a unique key to make the sort stable. Given your data, this would seem to be either duration, name, or both:

SELECT * 
ROM View_Name
ORDER BY BeginTime ASC, Duration, Name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Answer 2

your order by should be unique,otherwise you will get indeterministic results(in your case ,begin time is not unique and your are not guarnteed to get same results every time).try changing your query to below to make it unique..

SELECT * FROM View_Name ORDER BY duration OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

Further to add ,your first query (select * from view) result set is not guaranteed to be accurate every time unless you have an outer order by .