Разбиение на страницы в T-SQL

Работа с базами данных в .NET Framework --- Оконные функции T-SQL --- Разбиение на страницы

Потребность в разбиении на страницы очень часто требуется в приложениях. Обычно нужно предоставлять пользователю за раз только определенную часть строк из результирующего набора запроса, чтобы она умещалась на веб-странице, графическом интерфейсе или на экране. Для разбиения на страницы можно применить функцию ROW_NUMBER. Номера полученным в результате строкам присваиваются в нужном порядке, после чего отфильтровывается правильный диапазон номеров строк на основе заданного номера и размера страниц. Для оптимизации производительности желательно определить номера, полученные на основе элементов упорядочения окна, в качестве ключей индекса, а также включить в этот индекс остальные атрибуты, которые используются в запросах, для обеспечения покрытия запросов.

Допустим, к примеру, что нужно обеспечить разбиение на страницы заказов из таблицы Sales.Orders по атрибутам orderdate и упорядочение по orderid (с самых давних до самых новых) и вернуть в результирующем наборе атрибуты orderid, orderdate, custid и empid. В соответствии с изложенными выше рекомендациями создаем следующий индекс:

CREATE UNIQUE INDEX idx_od_oid_i_cid_eid
  ON Sales.Orders(orderdate, orderid)
  INCLUDE(custid, empid);

Затем, получив на вход номер и размер страницы, используем следующий код для фильтрации нужной страницы строк. Например, следующий код возвращает третью страницу при размере страницы равном 25 строкам, то есть нам нужны строки с 51 по 75:

DECLARE
  @pagenum  AS INT = 3,
  @pagesize AS INT = 25;

WITH C AS
(
  SELECT ROW_NUMBER() OVER( ORDER BY orderdate, orderid ) AS rownum,
    orderid, orderdate, custid, empid
  FROM Sales.Orders
)
SELECT orderid, orderdate, custid, empid
FROM C
WHERE rownum BETWEEN (@pagenum - 1) * @pagesize + 1
                 AND @pagenum * @pagesize
ORDER BY rownum;

На следующем рисунке показан план выполнения этого запроса:

Заметьте, что из-за того, что индекс обеспечил поддержку при вычислении ROW_NUMBER, серверу SQL Server не потребовалось просматривать все строки таблицы. Вместо этого он просмотрел только первые 75 строк индекса и отфильтровал строки с номерами с 51 по 75. Легко понять, что в отсутствие такого индекса SQL Server пришлось бы просмотреть и отсортировать все строки, назначить им номера, а затем отфильтровать. Так что в данной ситуации индексирование оказывается как нельзя более кстати.

Описанную методику, основанную на номерах строк, можно применять, начиная с версии SQL Server 2005. В SQL Server 2012 есть альтернативное решение, в котором используется новый параметр OFFSET/FETCH. Этот параметр похож на TOP за исключением того, что предусмотрен стандартом, поддерживает пропуски строк и является частью предложения ORDER BY. Вот код фильтрации для получения нужной страницы строк с использованием параметра OFFSET/FETCH, в качестве входных данных служат номер и размер страницы:

DECLARE
  @pagenum  AS INT = 3,
  @pagesize AS INT = 25;

SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate, orderid
OFFSET (@pagenum - 1) * @pagesize ROWS FETCH NEXT @pagesize ROWS ONLY;

План этого запроса показан на рисунке ниже:

Заметьте, что план выполнения оптимизируется аналогично тому, как это происходит в методике с использованием номеров строк — в том смысле, что SQL Server просматривает только первые 75 строк индекса и отфильтровывает только последние 25. В результате число операций чтения в этих двух случаях примерно одинаково.

По завершении надо выполнить следующий код очистки:

DROP INDEX idx_od_oid_i_cid_eid ON Sales.Orders;