MSSQL ORDER BY with OFFSET and FETCH NEXT do not work properly

I have this query:

SELECT TOP (1000) [id]
      ,[name]
      ,[dob]
FROM [Production].[dbo].[test]
ORDER by [dob] ASC

Result:

Now I want to get first 10 records:

SELECT [id]
      ,[name]
      ,[dob]
FROM [Production].[dbo].[test]
ORDER by [dob] ASC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

Result:

Now I want to get second 10 records:

SELECT [id]
      ,[name]
      ,[dob]
FROM [Production].[dbo].[test]
ORDER by [dob] ASC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

Result:

As you can see records:

are duplicated in first 10 records and second 10 records.
I understand that ORDER BY is messing around.

Solution could be adding another ORDER BY but what if have to use only one column to ORDER BY and some cells may have same value?

You must assure the ORDER BY clause gives a unique order to the sequence.

In your case, dob is not a unique key for the table. Add an order condition that makes it unique: [id] after the [dob] field.

eg.

SELECT [id]
      ,[name]
      ,[dob]
FROM [Production].[dbo].[test]
ORDER by [dob] ASC, [id]
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

> what if [I] have to use only one column to ORDER BY and some cells may have same value?

That's an artificial constraint you'll need to remove.

Relational databases explicitly disclaim any responsibility at all for preserving any inherent or natural order for data. There is no such thing as any default or fallback ordering. A database is free to return data in the order it deems most efficient for the situation, and without explicit ORDER BY instructions they can and will in some circumstances return different orders for the same query and source data from run to run.