Перейти до вмісту
Підготовка до співбесіди

SQL і T-SQL

Мова запитів очима розробника торговельної системи: з’єднання, групування, підзапити й CTE, віконні функції, процедури й функції, обробка множин замість циклів. Основний діалект — T-SQL; де PostgreSQL поводиться інакше, це сказано одним рядком, а подробиці — в темі «PostgreSQL проти SQL Server».

Junior+

Які бувають JOIN і чим вони відрізняються?

Section titled “Які бувають JOIN і чим вони відрізняються?”
Відповідь

Що це

JOIN з’єднує рядки двох таблиць за умовою в ON. Види відрізняються тим, що робити з рядками без пари.

Вид Що поверне
INNER JOIN лише рядки з парою
LEFT JOIN усі рядки зліва; немає пари — NULL справа
RIGHT JOIN те саме для правої таблиці
FULL JOIN усі рядки обох таблиць
CROSS JOIN усі комбінації, без умови

Як користуватися

  • Чеки з назвою магазину — INNER JOIN.
  • Усі магазини, навіть без продажів, — LEFT JOIN від магазинів до продажів.
  • Сітка «магазин × день» для звіту з нулями — CROSS JOIN з календарем.

Типова помилка

Умова на праву таблицю LEFT JOIN у WHERE: WHERE s.SoldOn = '2026-10-01'. У магазинів без продажів там NULL, порівняння не істинне, і вони зникнуть. Такий фільтр ставлять в ON.

Як влаштовано

  1. Сервер логічно будує всі пари рядків і залишає ті, де умова ON істинна.
  2. Зовнішнє з’єднання додає рядки збережуваної сторони без пари, з NULL на місці іншої сторони.
  3. Лише після цього WHERE фільтрує результат.

Тому для INNER JOIN умова в ON і в WHERE рівнозначна, а для LEFT JOIN — ні.

Коли що обирати

Задача Конструкція
Чи є пара, стовпці справа не потрібні EXISTS
Що є лише в одному з двох джерел FULL JOIN і перевірка на NULL
Top-N або функція від стовпців кожного рядка CROSS APPLY або OUTER APPLY

EXISTS — це напівз’єднання: рядок зліва повертається один раз, скільки б пар не знайшлося. CROSS APPLY поводиться як INNER JOIN, OUTER APPLY — як LEFT. RIGHT JOIN зазвичай переписують на LEFT — так легше читати.

Пастки

  • Розмноження рядків. З’єднання «один до багатьох» повторює рядок шапки для кожної деталі, і SUM(h.Total) за шапкою чека порахує суму кілька разів.
  • NULL у ключі. NULL = NULL не істинне, тому рядки з NULL у стовпці з’єднання пари не знаходять.
  • LEFT, потім INNER. A LEFT JOIN B ... JOIN C ON C.Id = B.CId відкине рядки A без B: внутрішнє з’єднання знову вимагає пари.

Правило

Фільтр за збережуваною стороною — у WHERE, за приєднуваною — в ON. Перевірка існування — EXISTS, а не JOIN із DISTINCT.

Під капотом

  • Логічний JOIN не задає алгоритм. Оптимізатор обирає Nested Loops (мало рядків ззовні, індекс усередині), Merge Join (обидва входи відсортовані за ключем) або Hash Match (великі невідсортовані входи). Докладно — в темі «Індекси й плани запитів».
  • APPLY логічно викликає праву частину для кожного рядка зліва. Підзапит або вбудовувана функція при цьому розкриваються в план, і оптимізатор може переписати їх на звичайне з’єднання.
  • Усунення з’єднань. LEFT JOIN до таблиці, чиї стовпці не використовуються, оптимізатор прибирає, якщо ключ справа унікальний. INNER JOIN — якщо є довірений (WITH CHECK) зовнішній ключ на стовпці NOT NULL.

Ціна

  • Розмноження рядків — це не лише неправильна сума, а й зайві читання та пам’ять на сортування й хеш-таблиці.
  • JOIN із DISTINCT спершу розмножує рядки, потім згортає їх сортуванням або хешем. EXISTS зупиняється на першій знайденій парі.

Компроміси

  • CROSS APPLY (SELECT TOP (1) ... ORDER BY ...) швидкий за індексу за ключем і датою та невеликої кількості зовнішніх рядків. На мільйонах рядків вигідніша нумерація одним проходом.
  • У PostgreSQL те саме пишуть як CROSS JOIN LATERAL і LEFT JOIN LATERAL ... ON true.

Як перевірити в продакшені

  • У фактичному плані порівнюють оцінку й реальну кількість рядків після з’єднання: зростання на порядок — ознака розмноження.
  • Якщо з’єднання усунуто, таблиці в плані просто немає; якщо вона там є — перевірте унікальність і довіреність ключів.

Підсумок: JOIN — логічна операція над множинами; алгоритм обирає оптимізатор, а кількість рядків і місце умови лишаються на вас.

T-SQL
CREATE TABLE #Stores (StoreId int PRIMARY KEY, Name varchar(20));
CREATE TABLE #Sales (SaleId int PRIMARY KEY, StoreId int, Amount int, SoldOn date);
INSERT #Stores VALUES (1, 'Center'), (2, 'Station'), (3, 'Outskirts');
INSERT #Sales VALUES (101, 1, 500, '2026-10-01'), (102, 2, 300, '2026-09-30'),
                     (103, NULL, 70, '2026-10-01');


-- Усі магазини та їхні продажі за 1 жовтня: фільтр за правою таблицею — в ON
SELECT st.Name, sa.Amount
FROM #Stores AS st
LEFT JOIN #Sales AS sa ON sa.StoreId = st.StoreId AND sa.SoldOn = '2026-10-01';
-- Center 500, Station NULL, Outskirts NULL


-- Той самий фільтр у WHERE відкидає рядки з NULL: LEFT JOIN став внутрішнім
SELECT st.Name, sa.Amount
FROM #Stores AS st
LEFT JOIN #Sales AS sa ON sa.StoreId = st.StoreId
WHERE sa.SoldOn = '2026-10-01';
-- Center 500


-- CROSS APPLY: останній продаж кожного магазину; магазини без продажів випадуть
SELECT st.Name, lastSale.Amount
FROM #Stores AS st
CROSS APPLY (SELECT TOP (1) sa.Amount FROM #Sales AS sa
             WHERE sa.StoreId = st.StoreId ORDER BY sa.SoldOn DESC) AS lastSale;
-- Center 500, Station 300
Джерела
Junior+

Як працюють GROUP BY і HAVING і в якому порядку логічно виконується SELECT?

Section titled “Як працюють GROUP BY і HAVING і в якому порядку логічно виконується SELECT?”
Відповідь

Що це

  • GROUP BY розбиває рядки на групи з однаковими значеннями вказаних стовпців.
  • Агрегатна функція згортає групу в одне значення: COUNT, SUM, AVG, MIN, MAX.
  • HAVING — фільтр за групами; у ньому можна використовувати агрегати.

Логічний порядок

  1. FROM і JOIN — джерело рядків.
  2. WHERE — фільтр рядків.
  3. GROUP BY — групи.
  4. HAVING — фільтр груп.
  5. SELECT — вирази й псевдоніми.
  6. DISTINCT, потім ORDER BY, потім TOP або OFFSET.

Як користуватися

Магазини з виторгом понад 100: GROUP BY StoreId, потім HAVING SUM(Amount) > 100 і ORDER BY Revenue DESC за псевдонімом.

Типова помилка

Написати WHERE SUM(Amount) > 100. На кроці WHERE груп ще немає — потрібен HAVING.

Як влаштовано

  • Після GROUP BY кожен рядок результату — група. У SELECT допустимі стовпці групування, агрегати й вирази над ними; будь-який інший стовпець — помилка.
  • Агрегати пропускають NULL: COUNT(*) рахує рядки, COUNT(Amount) — лише заповнені значення, AVG ділить на кількість не-NULL.
  • AVG від int у SQL Server повертає int: середнє 400,5 стане 400. Потрібен дріб — AVG(Amount * 1.0) або приведення до decimal.
  • Усі NULL у стовпці групування потрапляють в одну групу, хоча NULL = NULL не істинне.

Коли що обирати

Умова Куди ставити
На вихідні стовпці WHERE: менше рядків потрапить у групування
На агрегат HAVING
На псевдонім із SELECT обгорнути запит у підзапит і фільтрувати ззовні

Пастки

  • Умовна агрегація. Виторг за категоріями в одному рядку рахують через SUM(CASE WHEN Category = 'Milk' THEN Amount END). У PostgreSQL те саме коротше: SUM(Amount) FILTER (WHERE ...).
  • Порядок рядків. Його гарантує лише зовнішній ORDER BY. Без нього порядок будь-який, навіть якщо «завжди був за ключем».

Правило

Фільтруйте якомога раніше: WHERE до HAVING. Вираз, що повторюється, виносьте в підзапит або CROSS APPLY (VALUES (...)), а не копіюйте в SELECT і GROUP BY.

Під капотом

  • Логічний порядок — порядок зв’язування імен. Документація Microsoft прямо пише, що фізичний порядок обирає обробник запитів і він може відрізнятися.
  • Оптимізатор переносить умови HAVING без агрегатів нижче групування, а фільтри — ближче до читання таблиці.
  • Групування виконує Stream Aggregate (вхід відсортовано за ключами групування, наприклад індексом) або Hash Aggregate (хеш-таблиця в пам’яті).

Ціна

  • Hash Aggregate і сортування отримують виділення пам’яті до старту. За заниженої оцінки рядків дані скидаються в tempdb, і в плані з’являється попередження про spill.
  • Індекс, ключ якого починається зі стовпців GROUP BY, дає Stream Aggregate без сортування.

Компроміси

  • Порядок обчислення предикатів не гарантований. WHERE ISNUMERIC(Code) = 1 AND CAST(Code AS int) > 100 може впасти, надійно — TRY_CAST(Code AS int) > 100.
  • GROUPING SETS, ROLLUP і CUBE рахують підсумки й проміжні підсумки за один прохід. Функція GROUPING() відрізняє рядок підсумку від групи зі справжнім NULL.
  • PostgreSQL дозволяє псевдонім із SELECT у GROUP BY і стовпці, функціонально залежні від первинного ключа групи; SQL Server — ні.

Як перевірити в продакшені

  • У фактичному плані дивляться оператор агрегації й попередження про spill у Sort і Hash.
  • Велика розбіжність оцінки й факту перед агрегацією — привід перевірити статистику, докладно — в темі «Індекси й плани запитів».

Підсумок: логічний порядок пояснює видимість імен, а фізичний обирає оптимізатор, тому покладатися на порядок обчислення умов не можна.

T-SQL
-- Чеки: магазин і сума (NULL — чек ще не закритий)
SELECT StoreId,
       COUNT(*)      AS Receipts,   -- усі рядки групи
       COUNT(Amount) AS Closed,     -- лише не NULL
       SUM(Amount)   AS Revenue,
       AVG(Amount)   AS AvgCheck    -- int: дробова частина відкидається
FROM (VALUES (1, 500), (1, 301), (2, 70), (2, NULL), (3, 900), (4, 1000))
     AS r(StoreId, Amount)
WHERE StoreId <> 4                  -- фільтр рядків до групування
GROUP BY StoreId
HAVING SUM(Amount) > 100            -- фільтр груп після агрегації
ORDER BY Revenue DESC;              -- псевдонім із SELECT видно лише тут
-- StoreId Receipts Closed Revenue AvgCheck
-- 3       1        1      900     900
-- 1       2        2      801     400    (у PostgreSQL 400.5)
-- Магазин 2 (виторг 70) відсіяв HAVING, магазин 4 — WHERE.
-- WHERE Revenue > 100 дав би помилку: Invalid column name 'Revenue'
Джерела
Middle+

Підзапити й CTE: чим відрізняються, як працює рекурсія і чому NOT IN ламається на NULL?

Section titled “Підзапити й CTE: чим відрізняються, як працює рекурсія і чому NOT IN ламається на NULL?”
Відповідь

Що це

  • Підзапит — запит усередині запиту: скалярний (одне значення в SELECT), табличний (у FROM) або умова (IN, EXISTS у WHERE).
  • Корельований підзапит посилається на стовпці зовнішнього запиту: «продажі цього магазину».
  • CTE (common table expression, узагальнений табличний вираз) — підзапит з ім’ям: WITH Recent AS (SELECT ...) SELECT ... FROM Recent.
  • Рекурсивний CTE посилається сам на себе. Ним обходять дерева: категорії, оргструктуру, комплекти.

Навіщо

CTE розбиває складний запит на кроки зі зрозумілими іменами, і він читається згори донизу, як код.

Типова помилка

WHERE ProductId NOT IN (SELECT ProductId FROM Stops) за NULL у Stops повертає порожній результат: порівняння з NULL не дає ні «так», ні «ні». Використовуйте NOT EXISTS.

Як влаштовано

  1. Тризначна логіка. Умова буває TRUE, FALSE або UNKNOWN. Будь-яке порівняння з NULL, навіть NULL = NULL, дає UNKNOWN, а WHERE пропускає лише TRUE.
  2. NOT IN. x NOT IN (2, NULL) розкривається в x <> 2 AND x <> NULL. Друга частина — UNKNOWN, тому вся умова не TRUE для жодного рядка.
  3. Рекурсія. Спершу виконується якір. Потім рекурсивна частина з’єднується з результатом попереднього кроку, поки не поверне порожній набір. UNION ALL склеює всі кроки.
  4. Матеріалізація. CTE в SQL Server не зберігається: два посилання на нього — два виконання.

Коли що обирати

Задача Вибір
Чи є пов’язаний рядок EXISTS або NOT EXISTS
Короткий список констант IN (1, 2, 3)
Кроки складного запиту CTE
Проміжний результат потрібен багато разів тимчасова таблиця #t

Пастки

  • Інструкція перед WITH має закінчуватися ;, інакше синтаксична помилка.
  • MAXRECURSION за замовчуванням 100: глибше — помилка. OPTION (MAXRECURSION 0) знімає ліміт, але разом із ним і захист від зациклення.
  • Status <> 'Closed' не поверне рядки з NULL у Status. Потрібно додати OR Status IS NULL або, із SQL Server 2022, писати Status IS DISTINCT FROM 'Closed'.

Правило

Для «немає у списку» — NOT EXISTS. Стовпці, де NULL не має сенсу, оголошуйте NOT NULL.

Під капотом

  • CTE в SQL Server розгортається в запит, як представлення. За документацією результат не матеріалізується: кожне зовнішнє посилання виконує визначення заново.
  • У PostgreSQL 12+ CTE без побічних ефектів вбудовується, якщо на нього одне посилання, а за кількох обчислюється один раз. MATERIALIZED і NOT MATERIALIZED змінюють це рішення.
  • Рекурсивний CTE у плані — spool зі стеком (WITH STACK) і вкладені цикли: рекурсивна частина обробляє рядки по одному.
  • NOT IN за nullable-стовпцем додає в план перевірки на NULL; NOT EXISTS дає один anti semi join.

Ціна

  • CTE з трьома посиланнями тричі читає таблиці. Тимчасова таблиця обчислюється один раз і має статистику, а в табличної змінної статистики за стовпцями немає.
  • Рекурсія без індексу за ParentId читає таблицю на кожній ітерації.

Компроміси

Зберігання ієрархії Плюс Мінус
ParentId і рекурсивний CTE просто, легко змінювати обхід дорогий
hierarchyid піддерево — діапазон за індексом перенесення гілки дороге
Closure table предки й нащадки — звичайний JOIN окрема таблиця, підтримка під час змін

Тризначна логіка діє й в обмеженнях: CHECK відхиляє лише FALSE, тому NULL його проходить. Унікальний індекс SQL Server допускає один NULL, PostgreSQL — скільки завгодно, а з 15-ї версії можна NULLS NOT DISTINCT.

Як перевірити в продакшені

  • У плані шукають повторне читання однієї таблиці від різних посилань на CTE.
  • Підозрілі NOT IN (SELECT знаходить пошук за sys.sql_modules.definition.

Підсумок: CTE покращує читабельність, але не кешує результат, а NULL у запереченнях закривають NOT EXISTS або обмеженням NOT NULL.

T-SQL
DECLARE @Products TABLE (ProductId int NOT NULL);
DECLARE @Stops TABLE (ProductId int NULL);     -- стоп-лист із «порожнім» рядком
INSERT @Products VALUES (1), (2), (3);
INSERT @Stops VALUES (2), (NULL);


SELECT ProductId FROM @Products
WHERE ProductId NOT IN (SELECT ProductId FROM @Stops);
-- порожньо: 1 <> NULL дає UNKNOWN, і рядок відкидається


SELECT p.ProductId FROM @Products AS p
WHERE NOT EXISTS (SELECT 1 FROM @Stops AS s WHERE s.ProductId = p.ProductId);
-- 1, 3
T-SQL
-- Усі підкатегорії «Food» з рівнем вкладеності
WITH Categories AS (
    SELECT * FROM (VALUES (1, NULL, 'Food'), (2, 1, 'Dairy'), (3, 2, 'Cheese'),
                          (4, 1, 'Bakery'), (5, NULL, 'Household')) AS c(Id, ParentId, Name)
), Tree AS (
    SELECT Id, Name, 0 AS Lvl FROM Categories WHERE Name = 'Food'   -- якір
    UNION ALL
    SELECT c.Id, c.Name, t.Lvl + 1                                  -- рекурсивна частина
    FROM Categories AS c
    JOIN Tree AS t ON c.ParentId = t.Id
)
SELECT Id, Name, Lvl FROM Tree
OPTION (MAXRECURSION 100);   -- 100 і так за замовчуванням; 0 — без обмеження
-- Food 0, Dairy 1, Bakery 1, Cheese 2 (порядок рядків не гарантований)
Джерела
Middle+

Що таке віконні функції і які задачі вони розв’язують?

Section titled “Що таке віконні функції і які задачі вони розв’язують?”
Відповідь

Що це

Віконна функція — функція з OVER (...). Вона бачить «вікно» рядків довкола поточного й повертає значення для кожного рядка.

Функція Що робить
ROW_NUMBER() номер рядка: 1, 2, 3
RANK(), DENSE_RANK() місце з урахуванням рівних значень
LAG(), LEAD() значення з попереднього або наступного рядка
SUM() OVER (...) сума за вікном, наприклад накопичувальна

Як користуватися

  • PARTITION BY StoreId — рахувати окремо для кожного магазину.
  • ORDER BY SoldOn — у якому порядку йдуть рядки всередині магазину.
  • LAG(Amount) OVER (PARTITION BY StoreId ORDER BY SoldOn) — виторг минулого дня поруч із поточним.

Типова помилка

Написати WHERE ROW_NUMBER() OVER (...) = 1. Віконні функції дозволені лише в SELECT і ORDER BY, тому нумерують у CTE або підзапиті, а фільтрують ззовні.

Як влаштовано

  1. PARTITION BY ділить рядки на секції; у кожній розрахунок починається заново.
  2. ORDER BY задає порядок усередині секції.
  3. Рамка (ROWS або RANGE) обирає рядки відносно поточного. Якщо ORDER BY є, а рамки немає, діє RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Рівні значення

Функція Для виторгу 900, 700, 700, 300
ROW_NUMBER 1, 2, 3, 4 — порядок рівних не визначений
RANK 1, 2, 2, 4 — із пропуском
DENSE_RANK 1, 2, 2, 3 — без пропуску

Коли що обирати

  • Top-N у групі та видалення дублікатів — ROW_NUMBER у CTE й фільтр rn у зовнішньому запиті.
  • «Усі, хто ділить призові місця», — RANK або DENSE_RANK.
  • Зміна до минулого періоду — LAG; накопичувальний підсумок — SUM(...) OVER (... ROWS UNBOUNDED PRECEDING).

Пастки

  • RANGE за замовчуванням. Рядки з однаковим ключем ORDER BY потрапляють у рамку разом: два чеки за один день отримають однаковий «накопичувальний» підсумок.
  • LAST_VALUE. З рамкою за замовчуванням повертає поточний рядок. Потрібна рамка ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
  • Недетермінованість. ROW_NUMBER за неунікальним ключем нумерує рівні рядки довільно — додавайте в ORDER BY унікальний стовпець.

Правило

Для накопичувальних підсумків завжди пишіть рамку явно: ROWS UNBOUNDED PRECEDING.

Під капотом

  • Віконні функції обчислюються на кроці SELECT, після GROUP BY і HAVING, тому працюють поверх агрегатів: SUM(SUM(Amount)) OVER () — загальний виторг поруч із виторгом магазину.
  • Плану потрібен вхід, відсортований за PARTITION BY і ORDER BY: оператор Sort або індекс у потрібному порядку.
  • У row mode рамку тримає Window Spool: для ROWS з рамкою менше 10 000 рядків — у пам’яті, для RANGE і великих рамок — у робочій таблиці tempdb.
  • У batch mode агрегати рахує Window Aggregate без spool. Batch mode вмикається з columnstore, а із SQL Server 2019 і на звичайних таблицях за рівня сумісності 150.

Ціна

  • Сортування великого входу — виділення пам’яті й ризик spill у tempdb.
  • RANGE замість ROWS у накопичувальному підсумку на унікальному ключі дає той самий результат, але в рази більше читань.

Компроміси

  • POC-індекс (partition, order, covering: ключ зі стовпців PARTITION BY і ORDER BY, решта в INCLUDE) прибирає сортування, але здорожує запис.
  • Top-1 у групі через CROSS APPLY (SELECT TOP (1) ...) виграє, коли груп мало і є індекс; ROW_NUMBER — коли груп багато.
  • SQL Server 2022 додав WINDOW (рівень сумісності 160) та IGNORE NULLS для FIRST_VALUE, LAST_VALUE, LAG, LEAD. RANGE із числовим зсувом є в PostgreSQL 11+, але не в SQL Server.

Як перевірити в продакшені

  • У фактичному плані: Sort перед Window Spool, режим виконання (Batch або Row), попередження про spill.
  • SET STATISTICS IO ON: великі читання Worktable — ознака RANGE або широкої рамки.

Підсумок: віконна функція коштує сортування й spool; явна рамка ROWS, POC-індекс і batch mode роблять її дешевшою за самоз’єднання й курсори.

T-SQL
CREATE TABLE #Receipts (ReceiptId int PRIMARY KEY, StoreId int, SoldOn date, Amount int);
INSERT #Receipts VALUES
    (1, 1, '2026-10-01', 100), (2, 1, '2026-10-02', 150), (3, 1, '2026-10-02', 50),
    (4, 1, '2026-10-03', 120), (5, 2, '2026-10-01', 80),  (6, 2, '2026-10-02', 90);


SELECT ReceiptId, StoreId, Amount,
       SUM(Amount) OVER (PARTITION BY StoreId ORDER BY SoldOn, ReceiptId
                         ROWS UNBOUNDED PRECEDING) AS RunRows,
       SUM(Amount) OVER (PARTITION BY StoreId ORDER BY SoldOn) AS RunRange,  -- рамка RANGE
       Amount - LAG(Amount) OVER (PARTITION BY StoreId ORDER BY SoldOn, ReceiptId) AS Delta
FROM #Receipts;
-- ReceiptId StoreId Amount RunRows RunRange Delta
-- 1         1       100    100     100      NULL
-- 2         1       150    250     300      50     -- RANGE: обидва чеки за 02.10
-- 3         1       50     300     300      -100   -- отримують суму на кінець дня
-- 4         1       120    420     420      70
-- 5         2       80     80      80       NULL   -- нова секція: рахунок заново
-- 6         2       90     170     170      10
T-SQL
-- Топ-2 товари за виторгом у кожному магазині
WITH Ranked AS (
    SELECT StoreId, Product, Revenue,
           ROW_NUMBER() OVER (PARTITION BY StoreId ORDER BY Revenue DESC, Product) AS Rn,
           RANK()       OVER (PARTITION BY StoreId ORDER BY Revenue DESC) AS Rnk,
           DENSE_RANK() OVER (PARTITION BY StoreId ORDER BY Revenue DESC) AS DenseRnk
    FROM (VALUES (1, 'Milk', 900), (1, 'Bread', 700), (1, 'Eggs', 700), (1, 'Tea', 300),
                 (2, 'Milk', 400), (2, 'Coffee', 650)) AS t(StoreId, Product, Revenue)
)
SELECT StoreId, Product, Revenue, Rn, Rnk, DenseRnk
FROM Ranked
WHERE Rn <= 2;   -- у WHERE вихідного запиту віконну функцію використати не можна
-- 1 Milk   900 1 1 1
-- 1 Bread  700 2 2 2   -- Eggs відсічено: Rn = 3, хоча Rnk = 2; у Tea Rnk = 4, DenseRnk = 3
-- 2 Coffee 650 1 1 1
-- 2 Milk   400 2 2 2
Джерела
Middle+

Збережені процедури, функції, представлення й тригери: що коли використовувати?

Section titled “Збережені процедури, функції, представлення й тригери: що коли використовувати?”
Відповідь

Що це

Об’єкт Що робить Як викликати
Процедура будь-які інструкції, транзакції EXEC dbo.PostInvoice @id = 1
Скалярна функція повертає одне значення SELECT dbo.PriceWithVat(Price)
Таблична функція повертає таблицю FROM dbo.StoreSales(@storeId)
Представлення збережений SELECT FROM dbo.ActiveProducts
Тригер реагує на зміну таблиці викликається сам

Як користуватися

  • Бізнес-операція зі зміною даних — процедура.
  • Повторюваний відбір з параметром — таблична функція, без параметра — представлення.
  • Аудит і перевірки, які не виразити обмеженням, — тригер.

Типова помилка

Вважати, що тригер спрацьовує на кожен рядок. У SQL Server він спрацьовує один раз на інструкцію, а таблиці inserted і deleted містять усі змінені рядки. SELECT @id = Id FROM inserted мовчки обробить лише один.

Як влаштовано

  • Inline TVF — один вираз RETURN (SELECT ...). Оптимізатор підставляє його в запит, як представлення, і бачить індекси та статистику.
  • Багатоінструкційна TVF заповнює табличну змінну кількома інструкціями. Оптимізатор оцінює її в 100 рядків, а до SQL Server 2014 — в один.
  • Скалярна UDF до SQL Server 2019 викликається на кожен рядок і робить увесь план однопотоковим.
  • Процедура компілюється під час першого виклику; план кешується й перевикористовується.

Обробка помилок

  1. На початку процедури — SET XACT_ABORT, NOCOUNT ON.
  2. BEGIN TRY, BEGIN TRANSACTION, зміни, COMMIT.
  3. У BEGIN CATCH — IF @@TRANCOUNT > 0 ROLLBACK; і THROW;.

Без XACT_ABORT частина помилок відкочує лише одну інструкцію, транзакція триває, і можна зафіксувати половину операції.

Пастки

  • THROW враховує XACT_ABORT, RAISERROR — ні. У новому коді пишуть THROW, а інструкцію перед ним завершують ;.
  • Помилки компіляції й розв’язання імен на тому самому рівні TRY/CATCH не ловить.
  • Тригер AFTER виконується в тій самій транзакції: ROLLBACK у ньому скасовує й вихідну інструкцію.

Правило

Таблицю з функції повертайте лише через inline TVF. Багатоінструкційну переписуйте на inline або на процедуру з тимчасовою таблицею.

Під капотом

  • Скалярна UDF до SQL Server 2019 — інтерпретоване порядкове виконання. Оптимізатор майже не враховує її вартість, паралелізм заборонено.
  • SQL Server 2019 на рівні сумісності 150 перетворює придатну UDF на підзапит усередині плану. Не вбудовуються функції з GETDATE(), табличними змінними, CTE, кількома RETURN і низкою інших конструкцій; список обмежень зростав у накопичувальних оновленнях.
  • Із SQL Server 2017 (рівень 140) чергування виконання (interleaved execution) спершу матеріалізує багатоінструкційну TVF, а потім оптимізує решту запиту за реальною кількістю рядків. Працює лише в запитах на читання.

Ціна

  • SET STATISTICS IO не показує читань усередині скалярної UDF: запит виглядає дешевим, а витрачає хвилини.
  • Тригер подовжує транзакцію кожного запису в таблицю й ховає логіку від того, хто читає процедуру.

Компроміси

  • Inline TVF дає перевикористання без втрати прозорості, але всередині — лише один SELECT.
  • Індексоване представлення зберігає результат і прискорює агрегати, але здорожує кожен запис у базові таблиці.
  • Історію змін замість тригерів ведуть темпоральні таблиці (SQL Server 2016+), вивантаження змін — Change Tracking або CDC.
  • У PostgreSQL основна одиниця — функція; процедури з COMMIT усередині з’явилися в 11-й версії, а тригери бувають FOR EACH ROW і FOR EACH STATEMENT.

Як перевірити в продакшені

  • sys.dm_exec_function_stats (SQL Server 2016+) показує кількість викликів і CPU кожної невбудованої скалярної функції.
  • Відкриті після тайм-аутів транзакції видно в sys.dm_tran_session_transactions і DBCC OPENTRAN.

Підсумок: об’єкт обирають за тим, чи бачить оптимізатор його вміст, а XACT_ABORT ON закриває діру з тайм-аутами клієнта.

T-SQL
CREATE TABLE dbo.Stock (ProductId int PRIMARY KEY, Qty int NOT NULL CHECK (Qty >= 0));
INSERT dbo.Stock VALUES (1, 10), (2, 0);
GO
CREATE OR ALTER PROCEDURE dbo.MoveStock @from int, @to int, @qty int
AS
SET XACT_ABORT, NOCOUNT ON;  -- помилка виконання відкочує всю транзакцію
BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE dbo.Stock SET Qty = Qty + @qty WHERE ProductId = @to;
    UPDATE dbo.Stock SET Qty = Qty - @qty WHERE ProductId = @from;  -- порушить CHECK
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;  -- відкотити, якщо транзакція ще відкрита
    THROW;                        -- прокинути вихідну помилку клієнту
END CATCH;
GO
EXEC dbo.MoveStock @from = 2, @to = 1, @qty = 5;  -- помилка 547: конфлікт із CHECK
GO
SELECT ProductId, Qty FROM dbo.Stock;  -- 1: 10, 2: 0 — перший UPDATE теж відкотився
T-SQL · Senior
-- Які скалярні функції бази можна вбудувати (SQL Server 2019+)
SELECT o.name, m.is_inlineable
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON o.object_id = m.object_id
WHERE o.type = 'FN';  -- FN — скалярні, IF — inline TVF, TF — багатоінструкційні
Джерела
Senior+

Чим set-based підхід кращий за порядковий і як масово змінювати дані з .NET?

Section titled “Чим set-based підхід кращий за порядковий і як масово змінювати дані з .NET?”
Відповідь

Що це

  • Порядково — курсор або WHILE по рядках, а в C# — цикл, який на кожен рядок викликає окремий UPDATE.
  • Set-based — одна інструкція над набором: UPDATE ... FROM ... JOIN, INSERT ... SELECT, MERGE.

Навіщо

Кожна інструкція має сталу ціну: план, блокування, фіксація. Порядковий код платить її на кожному рядку, а із застосунку додає мережевий виклик: 100 000 викликів по 1 мс — 100 секунд самої лише мережі.

Як користуватися

  • Перерахувати ціни за прайсом — UPDATE p SET Price = n.Price FROM Products AS p JOIN NewPrices AS n ON ....
  • Передати список рядків із C# — табличний параметр, одним викликом.

Типова помилка

Курсор за звичкою процедурної мови: «для кожного товару знайти ціну й оновити». Майже будь-який такий цикл — одне з’єднання й один UPDATE.

Як влаштовано

  • Курсор тримає позицію в результаті й виконує тіло циклу на кожному рядку. Якщо він усе-таки потрібен, оголошуйте LOCAL FAST_FORWARD — однопрохідний і лише для читання.
  • MERGE за один прохід зіставляє джерело з цільовою таблицею й виконує UPDATE, INSERT або DELETE за гілками WHEN. OUTPUT $action показує, що зроблено з кожним рядком.
  • TVP — параметр табличного типу (CREATE TYPE ... AS TABLE); у процедурі він оголошується READONLY.

Пастки MERGE

  • Два рядки джерела на один рядок цілі — помилка: рядок не можна оновити двічі. Джерело має бути унікальним за ключем.
  • В ON — лише ключ зіставлення. Фільтр цілі в ON дає неправильні результати; фільтрують у гілках WHEN ... AND.
  • Без WITH (HOLDLOCK) паралельні upsert одного ключа ловлять порушення первинного ключа.
  • MERGE мусить закінчуватися ; (помилка 10713) і не проходить просту параметризацію, тому літерали замінюють параметрами.

Коли що обирати

Обсяг із .NET Спосіб
Кілька значень звичайні параметри
До тисяч рядків TVP або JSON з OPENJSON (SQL Server 2016+)
Сотні тисяч рядків SqlBulkCopy у проміжну таблицю, потім set-based

Правило

Курсор — лише там, де на кожен рядок потрібен окремий виклик: процедура, динамічний SQL по базах. Решта — однією інструкцією або порціями.

Під капотом

  • Кожна фіксація чекає запису журналу на диск. Порядковий INSERT в автофіксації — N скидань журналу й очікувань WRITELOG, одна інструкція — одне.
  • Set-based дає оптимізатору вибір: hash join замість N пошуків за індексом, паралелізм, batch mode.
  • Якщо одна інструкція бере 5000 і більше блокувань на одній таблиці чи індексі, SQL Server намагається замінити їх блокуванням усієї таблиці.

Компроміси

Підхід Плюс Мінус
Одна інструкція мінімум накладних витрат довга транзакція, зростання журналу, ескалація
Порції за ключем короткі блокування, журнал звільняється більше коду, немає загальної атомарності
Порядково контроль кожного рядка накладні витрати й round trip на рядок
  • Порцію обирають за діапазоном ключа або через TOP (N) з умовою за індексом. Інакше кожна порція заново сканує таблицю.
  • У моделі відновлення FULL журнал між порціями звільняють лише резервні копії журналу.
  • Upsert без MERGE: UPDATE ... WITH (UPDLOCK, SERIALIZABLE), за @@ROWCOUNT = 0 — INSERT, усе в одній транзакції. У PostgreSQL для цього є атомарний INSERT ... ON CONFLICT, а MERGE — з 15-ї версії.
  • TVP не має статистики за стовпцями: за великих обсягів дані перекладають у тимчасову таблицю.

Як перевірити в продакшені

  • Query Store або sys.dm_exec_query_stats: запит із мільйонами виконань по частці мілісекунди — порядкова обробка із застосунку.
  • Часті очікування WRITELOG — дрібні фіксації; sys.dm_exec_cursors показує відкриті курсори.

Підсумок: set-based прибирає накладні витрати на рядок, а порції не дають одній інструкції стати проблемою для всіх інших.

T-SQL
CREATE TABLE #SalesLog (Id int IDENTITY PRIMARY KEY, SoldAt datetime2 NOT NULL);
INSERT #SalesLog (SoldAt)
SELECT TOP (50000) DATEADD(DAY, -ABS(CHECKSUM(NEWID()) % 730), SYSDATETIME())
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;


DECLARE @batch int = 4000, @deleted int = 1;  -- порція нижче порогу ескалації
WHILE @deleted > 0
BEGIN
    DELETE TOP (@batch) FROM #SalesLog
    WHERE SoldAt < DATEADD(YEAR, -1, SYSDATETIME());
    SET @deleted = @@ROWCOUNT;                -- кожна порція — окрема транзакція
    -- WAITFOR DELAY '00:00:01';              -- пауза для OLTP-навантаження
END;
SELECT COUNT(*) AS LeftRows FROM #SalesLog;   -- лишилися продажі за останній рік
T-SQL
CREATE TABLE dbo.Prices (ProductId int PRIMARY KEY, Price decimal(10, 2) NOT NULL);
GO
-- Табличний тип: PRIMARY KEY не пустить дублікати в джерело MERGE
CREATE TYPE dbo.PriceList AS TABLE (ProductId int PRIMARY KEY, Price decimal(10, 2) NOT NULL);
GO
CREATE OR ALTER PROCEDURE dbo.UpsertPrices @Prices dbo.PriceList READONLY
AS
SET XACT_ABORT, NOCOUNT ON;
MERGE dbo.Prices WITH (HOLDLOCK) AS t   -- HOLDLOCK: без нього паралельні виклики
USING @Prices AS s                      -- ловлять порушення PRIMARY KEY
    ON t.ProductId = s.ProductId        -- в ON лише ключ зіставлення
WHEN MATCHED AND t.Price <> s.Price THEN
    UPDATE SET Price = s.Price
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductId, Price) VALUES (s.ProductId, s.Price);  -- ';' обов'язкова
GO
DECLARE @p dbo.PriceList;
INSERT @p VALUES (1, 49.90), (2, 15.00);
EXEC dbo.UpsertPrices @Prices = @p;
SELECT ProductId, Price FROM dbo.Prices;  -- 1: 49.90, 2: 15.00
C#
Microsoft.Data.SqlClient
using System.Data;
using Microsoft.Data.SqlClient;


// Стовпці та їхній порядок збігаються з типом dbo.PriceList на сервері
var prices = new DataTable();
prices.Columns.Add("ProductId", typeof(int));
prices.Columns.Add("Price", typeof(decimal));
prices.Rows.Add(1, 49.90m);
prices.Rows.Add(2, 15.00m);


await using var connection = new SqlConnection(
    "Server=.;Database=Shop;Integrated Security=true;TrustServerCertificate=true");
await connection.OpenAsync();


await using var command = new SqlCommand("dbo.UpsertPrices", connection)
{
    CommandType = CommandType.StoredProcedure
};
command.Parameters.Add(new SqlParameter("@Prices", SqlDbType.Structured)
{
    TypeName = "dbo.PriceList", // ім'я табличного типу на сервері
    Value = prices              // DataTable, DbDataReader або IEnumerable<SqlDataRecord>
});


// Один виклик і один MERGE на сервері замість N окремих UPDATE та INSERT
await command.ExecuteNonQueryAsync();
Джерела