T-SQL, Розбиття на сторінки
Потреба розбивки на сторінки дуже часто потрібна в додатках. Зазвичай потрібно надавати користувачеві лише певну частину рядків з результуючого набору запиту, щоб вона містилася на веб-сторінці, графічному інтерфейсі чи екрані. Для розбиття на сторінки можна застосувати функцію ROW_NUMBER. Номери отриманих в результаті рядків присвоюються в потрібному порядку, після чого фільтрується правильний діапазон номерів рядків на основі заданого номера і розміру сторінок. Для оптимізації продуктивності бажано визначити номери, отримані на основі елементів упорядкування вікна, як ключі індексу, а також включити до цього індексу інші атрибути, які використовуються в запитах, для забезпечення покриття запитів.
Припустимо, наприклад, що необхідно забезпечити розбиття на сторінки замовлень з таблиці Sales.Orders за атрибутами orderdate і впорядкування по orderid (з найдавніших до найновіших) і повернути в результуючому наборі атрибути orderid, orderdate, custid і empid. Відповідно до викладених вище рекомендацій створюємо наступний індекс:
Потім, отримавши на вхід номер та розмір сторінки, використовуємо наступний код для фільтрації потрібної сторінки рядків. Наприклад, наступний код повертає третю сторінку при розмірі сторінки, що дорівнює 25 рядкам, тобто нам потрібні рядки з 51 по 75:
На наступному малюнку показано план виконання цього запиту:
Описану методику, засновану на номерах рядків, можна застосовувати, починаючи з SQL Server 2005. У SQL Server 2012 є альтернативне рішення, в якому використовується новий параметр OFFSET/FETCH. Цей параметр схожий на TOP за винятком того, що передбачений стандартом, підтримує перепустки рядків і єчастиною пропозиції ORDER BY. Ось код фільтрації для отримання потрібної сторінки рядків з використанням параметра OFFSET/FETCH, як вхідні дані служать номер і розмір сторінки:
План цього запиту показано на малюнку нижче:
Зауважте, що план виконання оптимізується аналогічно тому, як це відбувається в методиці з використанням номерів рядків — у тому сенсі, що SQL Server переглядає лише перші 75 рядків індексу та відфільтровує лише останні 25. У результаті кількість операцій читання у цих двох випадках приблизно однакова.
Після завершення слід виконати наступний код очищення: