Перейти к содержимому
Подготовка к собеседованию

SQL и T-SQL

Язык запросов глазами разработчика торговой системы: соединения, группировка, подзапросы и CTE, оконные функции, процедуры и функции, обработка множеств вместо циклов. Основной диалект — T-SQL; где PostgreSQL ведёт себя иначе, это сказано одной строкой, а подробности — в теме «PostgreSQL против SQL Server».

Junior+

Какие бывают JOIN и чем они отличаются?

Заголовок раздела «Какие бывают 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 логически вызывает правую часть для каждой строки слева. Подзапрос или inline-функция при этом раскрываются в план, и оптимизатор волен переписать их в обычное соединение.
  • Устранение соединений. 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?

Заголовок раздела «Как работают 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?

Заголовок раздела «Подзапросы и 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+

Что такое оконные функции и какие задачи они решают?

Заголовок раздела «Что такое оконные функции и какие задачи они решают?»
Ответ

Что это

Оконная функция — функция с 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+

Хранимые процедуры, функции, представления и триггеры: что когда использовать?

Заголовок раздела «Хранимые процедуры, функции, представления и триггеры: что когда использовать?»
Ответ

Что это

Объект Что делает Как вызвать
Процедура любые инструкции, транзакции 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?

Заголовок раздела «Чем 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();
Источники