Пагинация в SQL Server: аналоги LIMIT и OFFSET в PostgreSQL

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

Для пропуска заданного количества строк и ограничения числа возвращаемых строк в SQL Server 2012+, применяйте команды ORDER BY, OFFSET и FETCH NEXT:

SELECT * FROM TableName
ORDER BY ColumnName
OFFSET 10 ROWS       -- Пропускаем первые 10 строк.
FETCH NEXT 5 ROWS ONLY; -- Затем выбираем только следующие 5 строк.

Это эквивалент оператора LIMIT 5 OFFSET 10, используемого в остальных SQL-диалектах. Важным условием является использование ORDER BY для корректной работы запроса.

Постраничное разделение в SQL Server 2012 и выше

В SQL Server 2012 и новее операции постраничного разделения данных сильно облегчены благодаря использованию команд OFFSET и FETCH NEXT:

DECLARE @PageSize INT = 10, @PageNumber INT = 2;

SELECT *
FROM YourTable
ORDER BY YourOrderColumn
OFFSET (@PageNumber – 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

Вместо плейсхолдеров YourTable и YourOrderColumn необходимо указать реальные имена таблицы и колонки.

Совместимость с предыдущими версиями: SQL Server 2005-2008

В версиях SQL Server от 2005 до 2008 R2, где отсутствуют OFFSET и FETCH NEXT, можно задействовать функцию ROW_NUMBER():

WITH NumberedTable AS (
    SELECT
        ROW_NUMBER() OVER(ORDER BY OrderColumn) AS RowNum,
        *
    FROM YourTable
)
SELECT *
FROM NumberedTable
WHERE RowNum BETWEEN @Offset AND (@Offset + @Limit – 1);

С помощью Общих Табличных Выражений (CTE) присваиваем строкам уникальные номера для последующего выбора заданного диапазона строк.

Работа с большими наборами данных: вопросы производительности

При работе с большими объемами данных важно учитывать производительность запросов:

• Для предварительного сокращения данных используйте фильтры WHERE.
• Создавайте индексы, которые будут оптимизировать операции сортировки и фильтрации.

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

Каждую строку в SQL Server можно сравнить с этажом в здании:

Этаж/Строка
⬆️
| 10-й | 🚁 | <- OFFSET пропускает до 10-го этажа.
|  9-й | ❌ |
...
|  1-й | ❌ |
⬇️

OFFSET устанавливает с какого "этажа" начинать, а FETCH NEXT определяет сколько "этажей" просмотреть (выбрать строк).

SELECT ColumnName
FROM TableName
ORDER BY ColumnName
OFFSET 9 ROWS       -- Пропустить 9 этажей.
FETCH NEXT 5 ROWS ONLY;  -- Выбрать следующие 5 этажей.

Устаревшие методы: влияние на производительность

До версий SQL Server 2012 широко применялся оператор SET ROWCOUNT. Однако его использование теперь не рекомендуется из-за устаревания:

SET ROWCOUNT @Limit;
SELECT *
FROM YourTable
ORDER BY YourOrderColumn;
SET ROWCOUNT 0;

Предпочтительнее использовать OFFSET и FETCH NEXT, или ROW_NUMBER(), чтобы обеспечить совместимость с более новыми версиями.

Создание динамической навигации по страницам

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

DECLARE @PageSize INT = 10, @PageNumber INT = 1;

SELECT *
FROM YourTable
ORDER BY YourOrderColumn
OFFSET (@PageNumber – 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

В старых версиях SQL Server применяйте аналогичную логику с использованием ROW_NUMBER().

Навигация по страницам и сортировка: улучшение взаимодействия с пользователем

Качественная навигация по страницам предполагает не только выборку данных, но и их логическую сортировку:

• Предложите пользователям различные варианты сортировки с помощью ORDER BY.
• Используйте CASE выражения или динамические SQL запросы для сложной сортировки.
• Оптимизируйте операции сортировки с помощью индексов для улучшения производительности.

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