Пагинация в SQL Server 2008: использование OFFSET и FETCH NEXT

Быстрый ответ

Для реализации постраничного вывода в SQL Server 2008 используется функция ROW_NUMBER(), применяемая вместе с Общим Табличным Выражением (Сommon Table Expression, или CTE). Ниже приведен пример простой выборки данных: выводим записи с 11 по 20 (а это, значит, вторая страница при разбиении на страницы по 10 записей каждая):

;WITH PagedUsers AS (
    SELECT ROW_NUMBER() OVER (ORDER BY UserID) AS RowNumber, * 
    FROM Users
)
SELECT *
FROM PagedUsers
WHERE RowNumber BETWEEN 11 AND 20;

Построение постраничного вывода: Пошаговое руководство

Разберем подробно подход к созданию постраничного вывода.

Сортировка по ключу: Важный первый шаг

Для правильной пагинации требуется отсортировать данные по уникальному столбцу, например, по UserID:

;WITH OrderedUsers AS (
    SELECT ROW_NUMBER() OVER (ORDER BY UserID) AS Row, * 
    FROM Users
)
SELECT *
FROM OrderedUsers
WHERE Row BETWEEN @PageStart AND @PageEnd;

Переменные @PageStart и @PageEnd определяют нужный нам диапазон.

Параметризация постраничного вывода для динамических результатов

Для динамичных изменений в пагинации используются параметры: размер страницы и номер текущей страницы:

DECLARE @PageSize INT = 10,
        @PageNumber INT = 2;
 
;WITH DynamicPagination AS (
    SELECT ROW_NUMBER() OVER (ORDER BY UserID) AS Row, * 
    FROM Users
)
SELECT *
FROM DynamicPagination 
WHERE Row BETWEEN (@PageNumber – 1) * @PageSize + 1 AND @PageNumber * @PageSize;

Приведенный выше код выписывает страницы, облегчая интерактивное навигирование.

Подсчет числа страниц: Определение общего количества страниц

Чтобы выяснить количество страниц, используйте функцию CEILING:

DECLARE @TotalRecords INT = (SELECT COUNT(*) FROM Users),
        @PageSize INT = 10;

SELECT CEILING(CAST(@TotalRecords AS FLOAT) / @PageSize) AS TotalPages;

Не стоит забывать: Проблемы производительности

Строгий выбор столбцов для ORDER BY и избегание использования неупорядоченных GUID могут значительно улучшить производительность. Всегда тестируйте решения на реальных объемах данных.

Визуализация

Постраничный вывод можно сравнить с перелистыванием толстой книги:

📖: [Страница 1, ..., Страница 100]

Текущая закладка: 🔖 Страница 20

Перейти к следующим страницам возможно без труда:

SELECT * FROM BigBook ORDER BY PageNumber OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

📖: [Страница 21, ..., Страница 30]

Таким образом, вы переключаетесь между страницами при помощи "OFFSET-FETCH".

Ловушки и подводные камни

Старайтесь избегать следующих ошибок при реализации пагинации:

Берегитесь с OFFSET и FETCH NEXT

SQL Server 2008 не поддерживает команды OFFSET и FETCH NEXT. Они доступны с версии 2012 и выше.

Не стоит путать ROWNUM и ROW_NUMBER()

Не впадайте в путаницу между ROWNUM из Oracle и ROW_NUMBER() из SQL Server – это две совершенно разные функции.

Готовность к масштабированию

Для приложений, где предполагается масштабирование, важно оптимизировать постраничный вывод:

Индексация

Индексация столбцов, используемых в ORDER BY, заметно улучшает производительность пагинации.

Keyset-пагинация: Решение с высокой производительностью

Keyset-пагинация остается отличным вариантом для работы с большими объемами данных в SQL Server 2008.

Полезные материалы

1. Реализация постраничного вывода в SQL Server – Stack Overflow – ресурс о пагинации в SQL Server 2008.
2. Постраничный вывод в SQL Server – обсуждение применения CTE для постраничного вывода.
3. Новые функции SQL Server STRING_AGG и STRING_SPLIT – информация о производительности и лучших практиках.
4. Динамический SQL – анализ преимуществ и недостатков динамического SQL в контексте пагинации.
5. Поддержка инструментов для keyset-пагинации – описание keyset-пагинации и ее влияние на производительность.