Время прочтения: 5 мин.

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

-- Создадим под эксперименты временную базу данных
CREATE DATABASE PoetsDataPaging GO
USE PoetsDataPaging GO
-- Создадим таблицу с тремя колонками "ID", "Имя", "Фамилия"
CREATE TABLE dbo.Poets
(Id INT PRIMARY KEY, FirstName NVARCHAR(50), LastName NVARCHAR(50))
-- Заполним таблицу именами поэтов серебряного века
INSERT INTO dbo.Poets VALUES
(1, 'Иван', 'Бунин'), (2, 'Марина', 'Цветаева'),
(3, 'Александр', 'Блок'), (4, 'Николай', 'Гумилёв'),
(5, 'Борис', 'Пастернак'), (6, 'Корней', 'Чуковский'),
(7, 'Сергей', 'Есенин'), (8,'Осип', 'Мандельштам')

Результат:

Пейджинг данных по OFFSET…FETCH

Создадим хранимую процедуру для подкачки данных, используя функцию разбиения на страницы OFFSET…FETCH. Эта хранимая процедура дает указанное количество строк (за это отвечает параметр @pageSize) для переданной страницы, номер (параметр @pageNum) и использует сортировку по столбцу FirstName.

CREATE PROCEDURE dbo.GetPoetsPagedDatabyFetch
(
  @pageNum INT,
  @pageSize INT
)
AS
BEGIN
    SELECT Id, FirstName, LastName
    FROM dbo.Poets WITH(NOLOCK)
    ORDER BY FirstName
    OFFSET (@pageNum - 1) * @pageSize ROWS
    FETCH NEXT @pageSize ROWS ONLY 
END

При помощи вышеуказанной процедуры получим данные первой страницы, отсортированные по имени. Для примера зададим параметр размера страницы равный «3»:

EXEC dbo.GetPoetsPagedDatabyFetch
         @pageNum = 1, @pageSize = 3

Результат:

Тоже самое выполним для данных второй страницы:

EXEC dbo.GetPoetsPagedDatabyFetch
         @pageNum = 2, @pageSize = 3

Результат:

И для данных третьей страницы:

EXEC dbo.GetPoetsPagedDatabyFetch
         @pageNum = 3, @pageSize = 3

Результат:

Ограничение полученных данных по данным третьей страницы обусловлено размером таблицы, к которой мы обращаемся. Тестовая таблица Poets содержит 8 записей, а следовательно, кратность данных страницы и данных в таблицы не согласуются. Это приводит к лагу количества полученных результатов по достижению конечной ёмкости таблицы в меньшую сторону. Это нужно учитывать!

Пейджинг данных по CTE

Реализуем метод разбиения на страницы, используя инструкцию CTE. Параметры сортировки оставим прежними. Для начала создадим хранимую процедуру:

CREATE PROCEDURE dbo.GetPoetsPagedData
(
    @pageNum INT,  -- Номер страницы
    @pageSize INT  -- Количество строк на странице
)
AS
BEGIN
    WITH PagingCTE AS
    (
     SELECT Id, FirstName, LastName, 
          ROW_NUMBER() OVER (ORDER BY FirstName) AS RowNumber
     FROM dbo.Poets WITH(NOLOCK)
    )
    SELECT *
    FROM PagingCTE
    WHERE RowNumber BETWEEN (@pageNum - 1) * @pageSize + 1 
              AND @pageNum * @pageSize  
END

Используя созданную процедуру, попробуем получить данные первой страницы, отсортированные по имени. Для примера, размер страницы сделаем равным «3»:

EXEC dbo.GetPoetsPagedData @pageNum = 1, @pageSize = 3

Результат:

Тоже самое выполним для данных второй страницы:

EXEC dbo.GetPoetsPagedData @pageNum = 2, @pageSize = 3

Результат:

И для данных третьей страницы:

EXEC dbo.GetPoetsPagedData @pageNum = 3, @pageSize = 3

Результат:

Особенность достижения пределов ёмкости данных таблицы, такая же, как и при пейджинге инструкцией OFFSET…FETCH.

Пейджинг данных по CTE cдинамической сортировкой столбцов

В предыдущем примере я разбил данные на страницы. В качестве столбца для сортировки я выбирал FirstName. Но в условиях решения реальных задач необходимы все столбцы для подкачки данных. Например, для таблицы Poets может потребоваться поддержка разбиения на страницы данных либо с именем, либо с фамилией, в качестве столбца сортировки. Для реализации данной задачи достаточно перечислить в запросе столбцы, по которым необходимо произвести сортировку извлекаемых данных через инструкцию CTE:

CREATE PROCEDURE dbo.GetPoetsDynamicSortColumn
(
    @pageNum INT,
    @pageSize INT,
    @sortColumnName VARCHAR(50)
)
AS
BEGIN
  WITH PagingCTE AS
  (
    SELECT Id, FirstName, LastName, ROW_NUMBER() OVER 
     (ORDER BY CASE
       WHEN @sortColumnName = 'FirstName' THEN FirstName 
       WHEN @sortColumnName = 'LastName' THEN LastName 
                         END) AS RowNumber
    FROM dbo.Poets WITH(NOLOCK)
  )
  SELECT *
  FROM PagingCTE
  WHERE RowNumber BETWEEN (@pageNum - 1) * @pageSize + 1 
   AND @pageNum * @pageSize
END

Используя созданную процедуру, попробуем получить данные первой страницы, отсортированные по столбцу FirstName. Для примера размер страницы сделаем равным «3»:

EXEC dbo.GetPoetsDynamicSortColumn
      @pageNum = 1, @pageSize = 3, @sortColumnName = 'FirstName'

Результат:

Теперь выполним процедуру для получения данных первой страницы (@pageSize = 3) и сортировкой по столбцу LastName:

EXEC dbo.GetPoetsDynamicSortColumn
      @pageNum = 1, @pageSize = 3, @sortColumnName = 'LastName'

Результат:

Используя инструкции OFFSET…FETCH и CFE мне удалось упорядочить результирующий набор запроса по заданному списку столбцов и ограничить число возвращаемых строк указанным диапазоном. Таким образом, можно получить более уникальный результат со сниженной ценой сетевых ресурсов, чем при использовании оператора TOP.