OFFSET-FETCH в T-SQL – опис і приклади використання

Починаючи з Microsoft SQL Server 2012 стало можливо використовувати конструкцію OFFSET-FETCH для реалізації посторінкового вибірки, зараз ми детально розглянемо цю конструкцію і розберемо приклади її використання.

До виходу SQL Server 2012 реалізувати посторінкову вибірку можна було, наприклад, з допомогою ранжирують функцій, приклад такого підходу можете подивитися в матеріалі – «Посторінкова вибірка на T-SQL – приклад реалізації».

У SQL Server 2012 можливості інструкції ORDER BY були розширені, а саме: додалася конструкція OFFSET-FETCH, яка як раз і дозволяє залишати в результуючому наборі тільки ту частину даних і то кількість рядків, яке нам необхідно.

Опис OFFSET-FETCH

OFFSET-FETCH – це конструкція мови Transact-SQL, яка є частиною ORDER BY, і дозволяє застосовувати фільтр до результуючому, вже відсортовані, набору даних.

OFFSET-FETCH призначена якраз для розбиття результуючого набору на частини (Сторінки), а також для обмеження кількості рядків.

У мові T-SQL є оператор TOP, за допомогою якого ми можемо приблизно так само обмежувати кількість рядків, що повертаються запитом. Однак TOP не дозволяє пропускати рядки, тобто ми завжди отримуємо рядки, починаючи з першої, враховуючи сортування, тобто за допомогою сортування ми можемо впливати на результат.

Використовуючи конструкцію OFFSET-FETCH, ми вже можемо пропускати певну кількість рядків.

Замітка! Всім тим, хто тільки починає своє знайомство з мовою T-SQL, рекомендую прочитати книгу «Шлях програміста T-SQL. Самовчитель по мові Transact-SQL », яку написав особисто я, і в якій я докладно, і в той же час простою мовою, розповідаю про мову T-SQL і про всі його конструкціях.

Спрощений синтаксис OFFSET-FETCH

ORDER BY Вираз для сортування

OFFSET Ціле число ROWS FETCH NEXT Ціле число ROWS ONLY

де,

  • ORDER BY – інструкція для сортування даних, що повертаються запитом;
  • OFFSET Ціле число ROWS – інструкція задає кількість рядків, які необхідно пропустити. Замість ROWS можна використовувати ключове слово ROW, вони еквіваленти. Однак ROWS і ROW можуть зробити код більш читабельним, наприклад, ROWS використовувати для пропуску декількох рядків, а ROW – для пропуску одного рядка (тобто однина і множина, але знову повторюся, вони взаємозамінні);
  • FETCH NEXT Ціле число ROWS ONLY – інструкція задає кількість рядків, які необхідно повернути, після обробки інструкції OFFSET. Замість ROWS можна використовувати ключове слово ROW, вони еквіваленти. Також замість NEXT можна використовувати ключове слово FIRST.

Важливі зауваження щодо використання OFFSET-FETCH

  • OFFSET-FETCH – це частина ORDER BY, без сортування використовувати конструкцію OFFSET-FETCH не вийде;
  • Інструкцію OFFSET можна використовувати без вказівки FETCH, а ось FETCH використовувати без вказівки OFFSET можна, тобто FETCH вимагає обов’язкової наявності OFFSET;
  • Чи не підтримується спільна робота операторів TOP і OFFSET-FETCH в одному запиті SELECT.

Приклади використання OFFSET-FETCH в T-SQL

Зараз давайте розглянемо кілька прикладів використання конструкції OFFSET-FETCH в мові T-SQL, але спочатку давайте визначимося з вихідними даними.

Вихідні дані для прикладів

Припустимо, у нас є таблиця TestTable, і вона містить наступні дані. Як сервер у мене виступає Microsoft SQL Server 2016 Express.

–створення таблиці
CREATE TABLE TestTable (
[ProductId] [INT] IDENTITY (1,1) NOT NULL,
[ProductName] [VARCHAR] (100) NOT NULL,
[Price] [Money] NULL
)
GO
–Добавленіе рядків в таблицю
INSERT INTO TestTable (ProductName, Price)
VALUES ( ‘Системний блок’, 300),
( ‘Монітор’, 200),
( ‘Клавіатура’, 100),
( ‘Миша’, 50),
( ‘Принтер’, 200),
( ‘Сканер’, 150),
( ‘Телефон’, 250),
( ‘Планшет’, 300)
GO
–Виборка даних
SELECT * FROM TestTable

OFFSET-FETCH – пропуск перших 3 рядків

У цьому прикладі ми пропустимо перші три рядки результуючого набору і повернемо всі наступні рядки. Для цього ми просто напишемо OFFSET 3 ROWS після визначення інструкції ORDER BY.

–Пропуск перших 3 рядків
SELECT * FROM TestTable
ORDER BY ProductId
OFFSET 3 ROWS

OFFSET-FETCH – пропуск перших 3 рядків і повернення наступних 3

В даному випадку ми також пропустимо перші три рядки, тільки додатково ми ще вкажемо інструкцію FETCH NEXT 3 ROWS ONLY, яка буде говорити SQL сервера про те, що потрібно повернути не всі наступні рядки, а тільки 3 наступні.

Як Ви розумієте, значення 3 в обох випадках можна змінювати на те значення, яке потрібно Вам, також замість константи (Тобто цифри 3) можна підставляти і змінні, і вирази, які повертають ціле значення.

Ссылка на основную публикацию