[SQL Server] Paging-repeated fields + primary keys

-- Test environment

If object_id ('tb') is not null drop table TB
Go
Create Table TB
(
Id int,
Name varchar (20 ),
Constraint pk_tb primary key (ID)
 
)
Go
Insert TB
Select 1, 'A' Union all
Select 2, 'B' Union all
Select 3, 'B' Union all
Select 4, 'B' Union all
Select 5, 'B' Union all
Select 6, 'B' Union all
Select 7, 'B' Union all
Select 8, 'B' Union all
Select 9, 'A' Union all
Select 10, 'A' Union all
Select 11, 'A'
Go
-- Pagination by name

-- Pagination by name
-- Query the first page
Select top 5 * from TB order by name, ID
-- Result
/*
ID name
-------------------------------
1
9
10
11
2 B

(5 rows affected)
*/

-- Query the second page
Select top 5 * from
(
Select top 10 * from TB order by name, ID
) T order by name DESC, Id DESC
-- Result
/*
ID name
-------------------------------
7 B
6 B
5 B
4 B
3 B

(5 rows affected)
*/

-- Query the second page
Select top 5 * from
(
Select top 15 * from TB order by name, ID
) T order by name DESC, Id DESC
-- Result
/*
ID name
-------------------------------
7 B
6 B
5 B
4 B
3 B

(5 rows affected)
*/