Как я могу выполнить разбиение курсора на страницы со строковыми столбцами и целочисленными значениями?

Всегда сортируйте, используя «уникальный ключ» при реализации внутренней нумерации страниц.

Тривиальный случай: вы сортируете по ID, это ключ.

При сортировке по другому критерию, который не является уникальным, сделайте его уникальным. Например, при сортировке по City добавьте ID в конце условия сортировки, например:

SELECT TOP(11) 
    c.*
FROM [Customers] AS [c]
ORDER BY 
C.City DESC, C.ID DESC -- added ID here

Добавляя ID в конце предложения ORDER BY, вы делаете столбцы сортировки уникальными. Таким образом, внутренняя нумерация страниц будет работать должным образом при получении следующей страницы и т. д., например:

SELECT TOP(11) 
    c.*
FROM [Customers] AS [c]
where c.city < 'City-99' or c.city = 'City-99' and ID <= 998999
ORDER BY 
C.City DESC, C.ID DESC

Поскольку SQL Server не реализует «кортежное неравенство», предикат в предложении WHERE выглядит неуклюжим. Однако это будет работать хорошо.

Примечание. Кортежное неравенство может перефразировать предикат поиска как (c.city, c.id) <= ('City-99', 998999). Они реализованы в DB2, PostgreSQL, MySQL, MariaDB. К сожалению, они не реализованы в Oracle или SQL Server. Они позволяют гораздо лучше оптимизировать запрос, если производительность является проблемой.

Разбивка на страницы в SQL Server может выполняться двумя разными способами в стандартном ISO SQL:

1) только с ORDER BY:

SELECT *
FROM   Customers
ORDER  BY City DESC, ID DESC OFFSET 0 ROWS FETCH NEXT 11 ROWS ONLY;

Чтобы разбить на страницы страницу из 10 строк и страницу 7, вы можете выполнить этот запрос:

SELECT *
FROM   Customers
ORDER  BY City DESC, ID DESC OFFSET 1 + 10 * 7  ROWS FETCH NEXT 10 ROWS ONLY;

В более общем смысле вы можете использовать переменные SQL:

DECLARE @PAGE INT = 7, @ROWS INT = 10;
SELECT *
FROM   Customers
ORDER  BY City DESC, ID DESC OFFSET 1 + @ROWS * @PAGE  ROWS FETCH NEXT @ROWS ROWS ONLY;

Если OFFSET/FETCH недоступен в вашей версии SQL Server...

2) Пагинация с помощью оконной функции ROW_NUMBER():

WITH 
QUERY AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY City DESC, ID DESC) AS ROWNUM
FROM   Customers
)
SELECT * 
FROM   QUERY
WHERE  ROWNUM <= 11
ORDER  BY 1 City DESC, ID DESC;

Чтобы разбить на страницы страницу из 10 строк и страницу 7, вы можете выполнить этот запрос:

WITH 
QUERY AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY City DESC, ID DESC) AS ROWNUM
FROM   Customers
)
SELECT * 
FROM   QUERY
WHERE  ROWNUM BETWEEN 1 + 10 * 7 AND (7 + 1) * 10
ORDER  BY 1 City DESC, ID DESC;

И вообще:

DECLARE @PAGE INT = 7, @ROWS INT = 10;
WITH 
QUERY AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY City DESC, ID DESC) AS ROWNUM
FROM   Customers
)
SELECT * 
FROM   QUERY
WHERE  ROWNUM BETWEEN 1 + @ROWS * @PAGE AND (@PAGE + 1) * @ROWS
ORDER  BY 1 City DESC, ID DESC;

Кстати, TOP — это нестандартный оператор, специфичный для MS SQL Server, и его не следует использовать, если какой-то стандарт SQL ISO может сделать то же самое или лучше...

Мне удалось найти решение, которое представляет собой комбинацию ответа @The Impaler и информации с веб-сайта use-the-index-luke.com, в частности этого лакомого кусочка:

SELECT *
  FROM ( SELECT *
           FROM sales
          WHERE sale_date <= ?
            AND NOT (sale_date = ? AND sale_id >= ?)
          ORDER BY sale_date DESC, sale_id DESC
       )
 WHERE rownum <= 10

Я выбрал два варианта: один с использованием смещения/предела, а другой с использованием подхода на основе курсора, который я искал.

Плохо работающий SQL был:

select c.* FROM customers c order by city asc, id asc offset 999989 rows FETCH NEXT 11 ROWS ONLY;

Объединив предыдущие ответы и предложения веб-сайта, я получил следующий SQL:

   SELECT top(10) * FROM ( SELECT * FROM customers   WHERE City <= 'City-99'
         AND NOT (City = 'City-99'  AND ID >= 999999 )) t  ORDER BY City DESC, ID DESC

Представление профилировщика SQL Server было еще более черно-белым.

                CPU / Reads / Duration
Offset / Limit 2049  22163    553
Cursor         1626  22163    274

Таким образом, хотя я и не уменьшил количество операций чтения между операторами, процессор и длительность были значительно увеличены.

Мне удалось выполнить сортировку по любому столбцу, использовать комбинацию ключей столбца сортировки и столбца идентификатора и получить довольно хорошую производительность, не индексируя ни один из столбцов.

Говоря об индексировании столбцов, я вернулся и поместил индекс в столбец «Город», чтобы посмотреть, будет ли это иметь значение:

                CPU / Reads / Duration
Offset / Limit 2156  22163    586
Cursor            0     48    48

При использовании того же SQL, описанного выше, улучшения становятся еще более радикальными.

И я понимаю, что правильная гигиена базы данных заключается в том, чтобы заранее знать, какие столбцы индексировать, но я выпускаю системы, которые не знают, как клиенты будут их использовать изначально, поэтому я настаиваю на чем-то лучше, чем Limit. / компенсировать. Я надеюсь, что мы уловим высокую частоту событий пользовательской сортировки, а затем порекомендуем индекс, если он не существует, для столбцов, по которым сортирует клиент в первую очередь.