Ответы на контрольные вопросы курса "Продвинутый MS SQL"

1. Урок 1. Оконные функции

1. Окно вычисляется на шаге SELECT уже по итогам FROM, WHERE и группировки: строки не схлопываются, а каждая получает дополнительное значение по набору соседей. Группировка сворачивает строки в одну. Фильтр в WHERE невозможен, потому что на шаге WHERE значение окна ещё не вычислено.
2. Для отчёта «три лучших с учётом ничьих» выбирается RANK: равные значения получают равные места, и все кандидаты попадают в выборку. ROW_NUMBER отрезает ровно три строки и при ничьей выбирает произвольно.
3. По умолчанию фрейм тянется от начала окна до текущей строки, поэтому «последнее» совпадает с текущим. Лечится полным фреймом ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING либо функцией FIRST_VALUE с сортировкой по убыванию.
4. ROWS считает физические строки и потому годится для среднего по числу строк; RANGE работает логическими границами и объединяет пэры с равным значением порядка.
5. Честное среднее восстанавливается сплошным календарём из таблицы чисел, левым соединением с данными и заменой отсутствующих значений нулём до вычисления фрейма.

2. Урок 2. CTE и рекурсия

1. Выражение действует ровно один следующий оператор. Оно не материализуется: оптимизатор разворачивает текст как подзапрос, поэтому план не отличается от плана вложенного запроса.
2. Сначала переназначаются зависимые строки потомков на канонический номер, затем удаляются дубликаты, и весь пакет исполняется в одной транзакции с XACT_ABORT: иначе внешний ключ отклонит удаление или останутся строки-сироты.
3. Якорь задаёт стартовое множество, UNION ALL соединяет, рекурсивная часть ссылается на само выражение и повторяется до пустого шага. В рекурсивной части запрещены агрегаты, TOP и DISTINCT; типы и длины столбцов обеих частей обязаны совпадать.
4. UNION устраняет дубликаты и потому добавляет сортировку или хэширование на каждой итерации; это и дорого, и маскирует циклы вместо их обнаружения.
5. При цикле поддерево становится недостижимым из корня: запрос дерева выполняется без ошибки и просто молча теряет ветку. Поэтому детектор циклов по накопленному пути обязателен до публикации иерархических справочников.

3. Урок 3. PIVOT, JSON и XML

1. PIVOT неявно группирует по всем столбцам источника, которые не являются значением и столбцом разворота; подача лишних столбцов дробит итоги. Поэтому источник ограничивается подзапросом ровно с тремя наборами данных.
2. Условная агрегация читается проще, не требует списка столбцов заранее и свободно комбинируется с другими агрегатами. Оператор удобен, когда список фиксирован и важна компактность записи.
3. Идентификаторы столбцов проходят через QUOTENAME, значения передаются только параметрами процедуры sp_executesql; конкатенация значений в текст запроса исключена.
4. JSON_VALUE возвращает скаляр до четырёх тысяч символов, JSON_QUERY — вложенный объект или массив, OPENJSON — набор строк с типизацией через WITH и единственный способ прочитать длинные значения без обрезки.
5. Индекс [1] объявляет синглтон: метод value обязан вернуть ровно один узел. Имена узлов XML регистрозависимы, поэтому путь пишется в точном регистре документа.

4. Урок 4. Планы выполнения и оптимизация запросов

1. Оценочный план строится по статистике до выполнения и содержит ожидаемые числа строк, фактический — замеренные при выполнении вместе с переполнениями в tempdb. DataGrip показывает оценочный план; для урока его достаточно вместе со статистикой чтения.
2. Число logical reads не зависит от состояния дискового кэша и загрузки машины, поэтому стабильно между прогонами; время меряет то, что изменилось снаружи запроса.
3. Поисковый предикат оставляет столбец сравнения «чистым» и позволяет выполнить поиск по индексу. Функция от столбца превращает предикат в невычислимое навигацией выражение и вынуждает сканирование.
4. Дочитка возникает, когда индекс нашёл строки, но не покрыл список столбцов. Столбцы покрытия идут в INCLUDE, потому что они не участвуют в навигации по дереву: расширение ключа меняло бы порядок, уникальность и стоимость обновления.
5. План процедуры строится под значение параметра первого вызова. Обезвреживается подсказкой RECOMPILE с ценой повторной компиляции, OPTIMIZE FOR под стабильное значение или ветвлением логики процедуры под класс значений.

5. Урок 5. Индексы

1. Ключ служит навигацией и задаёт порядок, INCLUDE — лежит на листовом уровне только для покрытия. Поэтому предикаты, соединения и сортировка идут в ключ, а столбцы списка выборки — в покрытие.
2. Равенство фильтра ставится первым столбцом ключа, столбец сортировки — вторым и в том же направлении: строки выходят из поиска уже упорядоченными, и оператор сортировки в плане исчезает.
3. При построении плана значение параметра неизвестно, и доказать вхождение в подмножество фильтрованного индекса нельзя. Лечится литералом, подсказкой RECOMPILE или отдельной веткой кода.
4. Рекомендации не видят существующих похожих индексов, не ранжируют порядок столбцов покрытия и плодят «почти одинаковые» варианты; оценка ранжирует очередь анализа, а не приказ на создание.
5. Перестроение дорого, пересоздаёт индекс и заодно обновляет статистику; реорганизация экономична и идёт в сети, но статистику не трогает — её обновляют отдельно.

6. Урок 6. Программирование на T-SQL

1. Триггер срабатывает на оператор, поэтому inserted и deleted — наборы строк. Чтение одной строки в переменную фиксирует одно произвольное значение пакета, и массовое изменение теряет аудит для остальных строк: обработка ведётся соединением наборов.
2. XACT_ABORT ON откатывает открытую транзакцию при ошибке выполнения; без него транзакция может остаться открытой, а следующий вызов унаследует её вместе с блокировками.
3. Единица означает живую согласованную транзакцию, ноль — отсутствие транзакции, минус один — обречённую транзакцию, которую можно только откатить; ошибки компиляции пакета ставят именно обречённое состояние.
4. Встраиваемая функция разворачивается в общий план с оценками и параллелизмом; многооператорная видится оптимизатору чёрным ящиком с константной оценкой строк и потому мешает построению плана.
5. Набор условий собирается только из заданных фильтров, значения идут параметрами sp_executesql, идентификаторы — через QUOTENAME. Пустой CATCH возвращает вызывающему ложный успех и запрещён везде, где важна целостность.

7. Урок 7. Транзакции и конкурентность

1. Грязное чтение, неповторяемое чтение и фантомы. Блокировочный уровень SERIALIZABLE устраняет все три за счёт удержания разделяемых и диапазонных блокировок до конца транзакции; версионирование устраняет их же без блокировок чтения.
2. Обе сессии читают одно значение до изменения и записывают на его основе: одно изменение затирает другое. Атомарное изменение в одном предикате не читает промежуточного значения вообще, поэтому терять нечего.
3. Сервер выбирает жертву по цене отката и завершает её ошибкой 1205, остальные продолжаются. Первые меры — единый порядок обращения к ресурсам и короткие транзакции с выносом чтения за пределы конкурентного участка.
4. При RCSI версия берётся на начало каждого оператора; при уровне SNAPSHOT — на начало транзакции. Отсюда разное поведение при длинных транзакциях и конфликт обновления 3960 только у SNAPSHOT.
5. При сканировании в порядке страниц во время разделения страниц строка может быть прочитана дважды или пропущена: отчёт по NOLOCK расходится с самим собой при повторе.

8. Урок 8. Секционирование больших таблиц

1. Функция задаёт границы значений, схема распределяет секции по файловым группам, таблица строится на схеме по столбцу секционирования; секций всегда на одну больше границ.
2. При RANGE RIGHT граница принадлежит правой секции: первое число месяца попадает в секцию своего месяца, и граница читается как начало периода.
3. Запросы ускоряются лишь побочно — от чтения меньшего числа секций; основной выигрыш в управляемости: архивное переключение метаданными, обслуживание частей и быстрая массовая загрузка. Без индексов и статистики секции скорость не дают.
4. Принимающая таблица — зеркало: та же структура столбцов, тот же набор индексов, проверка CHECK в пределах выводимой секции и хранение на той же файловой группе, что и секция.
5. Разделение непустой секции перемещает строки журналируемо и под блокировкой; пустая граничная секция делает добавление метаданичным. Поэтому NEXT USED назначается заранее, а хвост окна всегда пуст.

9. Урок 9. Финальный проект

Проект оценивается по критериям приёмки, а не по единственному верному решению. Ориентиры для разбора:

10. Что дальше

Навигация по материалам курса:

• Первый урок курса: 1. Оконные функции
• Обзор программы и подготовка базы: статья «Содержание курса».

Перед закрытием справочника отметьте выполненное:

• Все контрольные вопросы проговорены или записаны до сверки
• Все расхождения разобраны повторным выполнением примера
• Чек-листы всех уроков заполнены

Проверено: 23.10.2026