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

Проектирование и эксплуатация

Как спроектировать схему, которая переживёт годы роста, и как эксплуатировать базу под нагрузкой: нормальные формы и ключи, загрузки и отчёты, партиционирование и изменение схемы на живой системе. На уровне Senior это разговор о компромиссах и рисках, а не о синтаксисе.

Перед этой темой: Индексы и планы запросов, Транзакции и блокировки.

Junior+

Что такое нормализация и когда оправдана денормализация?

Заголовок раздела «Что такое нормализация и когда оправдана денормализация?»
Ответ

Что это

Нормализация — проектирование таблиц так, чтобы один факт хранился в одном месте. Название магазина лежит в таблице Store, а продажа ссылается на него по StoreId.

Нормальные формы

  1. 1НФ. В ячейке одно значение, нет повторяющихся групп: не колонки Phone1, Phone2, Phone3, а отдельная таблица телефонов.
  2. 2НФ. При составном ключе колонка зависит от всего ключа. В строке заказа (OrderId, ProductId) название товара зависит только от ProductId — ему место в Product.
  3. 3НФ. Неключевая колонка не зависит от другой неключевой. Адрес магазина зависит от магазина, а не от продажи.

Частая ошибка

Хранить список через запятую в одной колонке: 'A12,B7,C3'. Такой список не проиндексировать, а поиск превращается в LIKE '%B7%'.

Как устроено

Нормальные формы убирают три аномалии:

  • вставки — нельзя завести магазин, пока у него нет продаж;
  • обновления — адрес магазина надо менять в тысячах строк, и часть копий останется старой;
  • удаления — удалили последнюю продажу и потеряли данные о магазине.
Форма Требование
1НФ атомарные значения, нет повторяющихся групп
2НФ неключевые колонки зависят от всего составного ключа
3НФ неключевые колонки не зависят друг от друга
BCNF любой детерминант — потенциальный ключ

Когда что выбирать

  • Транзакционная база — 3НФ. Запись дешёвая, данные непротиворечивы, чтение ускоряют индексы.
  • Денормализация — когда измерено, что соединения или агрегаты на горячем пути дороги: итог в шапке чека, остаток товара, отчётная таблица.

Ловушки

  • История — не дубль. Цена в строке чека — цена на момент продажи, отдельный факт. Ссылка на текущую цену из справочника исказила бы старые чеки.
  • Копия без владельца. Денормализованную колонку меняют из нескольких мест, и она расходится с источником.

Правило

Сначала 3НФ, денормализация — по измерениям и с явным механизмом синхронизации.

Под капотом

  • Функциональная зависимость X → Y: значение X однозначно определяет Y. 3НФ допускает зависимость, если Y входит в потенциальный ключ; BCNF запрещает и её.
  • Расхождение 3НФ и BCNF бывает только при нескольких пересекающихся составных ключах, поэтому на практике таблица в 3НФ почти всегда уже в BCNF.

Цена денормализации

Способ Кто синхронизирует Цена
Вычисляемая колонка PERSISTED движок только выражения по своей строке
Индексированное представление движок, в той же транзакции дороже запись в базовые таблицы, много ограничений
Колонка-агрегат: итог, остаток код или триггер горячая строка, риск расхождения
Отчётная таблица загрузка по расписанию данные отстают

Компромиссы

  • Денормализация переносит стоимость с чтения на запись. Остаток товара в одной строке становится точкой конкуренции: все продажи товара обновляют её.
  • Отчётные базы денормализуют намеренно: их пишет одна загрузка, а читают многие запросы.
  • Перебор тоже вреден. Модель EAV («сущность — атрибут — значение») ради гибкости теряет типы и ограничения, а каждый атрибут превращается в соединение.

Как проверить в проде

  • Регулярная сверка: агрегат против пересчёта из исходных строк.
  • Горячая строка-агрегат видна по ожиданиям блокировок LCK_M_U и LCK_M_X на одном ключе.

Итог: нормализуют ради дешёвой и непротиворечивой записи; денормализуют точечно, когда ясно, кто и как синхронизирует копию.

T-SQL
-- До: название и адрес магазина повторяются в каждой продаже (нарушение 3НФ)
CREATE TABLE dbo.SaleFlat (
    SaleId       bigint        NOT NULL PRIMARY KEY,
    StoreId      int           NOT NULL,
    StoreName    nvarchar(100) NOT NULL,
    StoreAddress nvarchar(200) NOT NULL,
    Amount       decimal(12,2) NOT NULL
);


-- После: факт о магазине хранится один раз
CREATE TABLE dbo.Store (
    StoreId int           NOT NULL PRIMARY KEY,
    Name    nvarchar(100) NOT NULL,
    Address nvarchar(200) NOT NULL
);


CREATE TABLE dbo.Sale (
    SaleId  bigint        NOT NULL PRIMARY KEY,
    StoreId int           NOT NULL REFERENCES dbo.Store (StoreId),
    SoldAt  datetime2(0)  NOT NULL,
    Amount  decimal(12,2) NOT NULL
);


-- Переезд магазина: одна строка вместо тысяч
UPDATE dbo.Store SET Address = N'New address' WHERE StoreId = 42;
Источники
Junior+

Какой первичный ключ выбрать: identity, GUID или естественный ключ?

Заголовок раздела «Какой первичный ключ выбрать: identity, GUID или естественный ключ?»
Ответ

Что это

Вид ключа Пример
Суррогатный числовой int или bigint IDENTITY
Суррогатный GUID uniqueidentifier
Естественный код товара, ИНН, код магазина

Как пользоваться

  • По умолчанию — bigint IDENTITY(1, 1): компактный, растёт по порядку, не меняется.
  • Естественный ключ остаётся колонкой с ограничением UNIQUE: дубли запрещены, но внешние ключи ссылаются на суррогатный.
  • GUID — когда идентификатор нужен до вставки: его создаёт приложение или другая база.

Частая ошибка

Сделать первичным ключом штрихкод или ИНН. Штрихкоды переиспользуют, ИНН вводят с ошибкой, и менять приходится ключ во всех ссылающихся таблицах.

Как устроено

  • Первичный ключ по умолчанию создаёт кластерный индекс: строки таблицы хранятся упорядоченными по ключу.
  • Ключ кластерного индекса служит указателем на строку в каждом некластерном индексе. GUID на 16 байт вместо bigint на 8 увеличивает все индексы таблицы.
  • Растущий ключ дописывает строки в последнюю страницу. Случайный NEWID() вставляет в середину: страница делится пополам, индекс фрагментируется и заполнен наполовину.

Когда что выбирать

Ключ Когда
bigint IDENTITY по умолчанию, одна база
SEQUENCE номер нужен до вставки или общий для нескольких таблиц
Последовательный GUID id создаёт приложение, данные сливаются из нескольких баз
NEWID() почти никогда как кластерный ключ

Ловушки

  • Дыры в IDENTITY. Откат транзакции или перезапуск сервера оставляют пропуски. Номер чека без пропусков так не получить.
  • NEWSEQUENTIALID() работает только в DEFAULT, а после перезапуска Windows может начать с меньшего значения.
  • UUIDv7. Guid.CreateVersion7() упорядочен по времени для .NET, но не для uniqueidentifier в SQL Server.

Правило

bigint IDENTITY по умолчанию; GUID — когда id создаёт приложение, и тогда последовательный в порядке SQL Server.

Под капотом

  • SQL Server сравнивает uniqueidentifier не слева направо: старшими считаются последние 6 байт. В UUIDv7 время лежит в первых 6 байтах, поэтому порядок вставки в индекс почти случайный.
  • NEWSEQUENTIALID() и SequentialGuidValueGenerator (генератор EF Core по умолчанию для SQL Server) переставляют байты под этот порядок.
  • UUIDv7 сохраняет порядок в binary(16), записанный в big-endian: ToByteArray(bigEndian: true) (.NET 8+). binary сравнивается побайтно.

Цена

  • Растущий ключ на горячей таблице упирается в конкуренцию за последнюю страницу (PAGELATCH_EX). OPTIMIZE_FOR_SEQUENTIAL_KEY (SQL Server 2019) её сглаживает.
  • GUID — 16 байт в каждом индексе и внешнем ключе: на сотнях миллионов строк это гигабайты буферного пула.
  • Последовательные id легко угадать: публичный номер заказа отделяют от внутреннего ключа.

Компромиссы

  • Естественный ключ экономит соединения, но его смена каскадом идёт по всем ссылкам. Исключение — короткие неизменные коды вроде ISO-кода валюты.
  • Первичный ключ не обязан быть кластерным: GUID-ключ делают некластерным, а кластеризуют по растущей колонке.
  • IDENTITY известен только после вставки, поэтому пакетная вставка связанных строк требует OUTPUT или SEQUENCE.

Как проверить в проде

sys.dm_db_index_physical_stats показывает фрагментацию и заполненность страниц (avg_page_space_used_in_percent), а ожидания PAGELATCH_EX на одной странице — конкуренцию за конец индекса.

Итог: ключ выбирают по размеру, порядку вставки и месту генерации; «последовательный» GUID должен быть последовательным в порядке сравнения SQL Server.

T-SQL
CREATE TABLE dbo.Product (
    -- Суррогатный ключ: 8 байт, растёт по порядку, не меняется
    ProductId bigint IDENTITY(1, 1) NOT NULL
        CONSTRAINT PK_Product PRIMARY KEY CLUSTERED,
    -- Естественный ключ: уникален, но внешние ключи на него не ссылаются
    Sku       varchar(20)   NOT NULL CONSTRAINT UQ_Product_Sku UNIQUE,
    Name      nvarchar(200) NOT NULL
);


CREATE TABLE dbo.Receipt (
    -- GUID, последовательный в порядке сравнения SQL Server
    ReceiptId uniqueidentifier NOT NULL
        CONSTRAINT DF_Receipt_Id DEFAULT NEWSEQUENTIALID()
        CONSTRAINT PK_Receipt PRIMARY KEY CLUSTERED,
    StoreId   int          NOT NULL,
    CreatedAt datetime2(0) NOT NULL
);


-- Горячая вставка в конец индекса (SQL Server 2019+)
ALTER INDEX PK_Product ON dbo.Product SET (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);
C#
using System.Data.SqlTypes;


var ids = new List<Guid>();
for (var i = 0; i < 5; i++)
{
    ids.Add(Guid.CreateVersion7()); // .NET 9+: время в мс + случайные биты
    Thread.Sleep(2);                // внутри одной мс порядок не гарантирован
}


Console.WriteLine(ids[0].Version);                 // 7
Console.WriteLine(ids.SequenceEqual(ids.Order())); // True: в .NET порядок по времени


// SqlGuid сравнивает как SQL Server: старшие — последние 6 байт, а там случайные биты
var asSqlServer = ids.OrderBy(id => new SqlGuid(id));
Console.WriteLine(ids.SequenceEqual(asSqlServer)); // False (почти всегда)


// binary(16) сравнивается побайтно: big-endian сохраняет порядок по времени
var asBinary = ids.OrderBy(id => Convert.ToHexString(id.ToByteArray(bigEndian: true)), StringComparer.Ordinal);
Console.WriteLine(ids.SequenceEqual(asBinary));    // True

SqlGuid из System.Data.SqlTypes повторяет правила сравнения SQL Server, поэтому показывает, как UUIDv7 ляжет в индекс по uniqueidentifier.

Источники
Middle+

Как устроен ETL и как строят загрузки в SSIS?

Заголовок раздела «Как устроен ETL и как строят загрузки в SSIS?»
Ответ

Что это

ETL — три шага:

  1. Extract — прочитать данные из источника: базы, файла, API.
  2. Transform — очистить и привести к формату цели: типы, справочники, дубли.
  3. Load — записать в целевую базу.

Как устроен пакет SSIS

  • Control flow — задачи и порядок их выполнения: «очистить staging», «загрузить данные», «выполнить процедуру».
  • Data flow — задача, через которую идут строки: источник → преобразования → приёмник.

Как пользоваться

Пакет разрабатывают в Visual Studio, разворачивают на сервер в каталог SSISDB и запускают по расписанию заданием SQL Server Agent.

Частая ошибка

Каждый раз перезагружать всю таблицу. Пока данных мало, это работает, а через год загрузка перестаёт помещаться в ночное окно.

Как устроено

Инкрементальная загрузка по шагам:

  1. Прочитать из управляющей таблицы последний водяной знак.
  2. Data flow забирает строки новее него и быстрой вставкой кладёт в staging.
  3. Процедура переносит staging в цель через MERGE или UPDATE и INSERT.
  4. В той же транзакции сохранить новый водяной знак.

Когда что выбирать

Способ Плюс Минус
Водяной знак просто, без настройки источника не видит удалений
Change tracking дёшево, видит удаления только ключи, без старых значений
CDC значения до и после из журнала SQL Server Agent, нагрузка на журнал

Ловушки

  • Построчная обработка. OLE DB Command и Slowly Changing Dimension выполняют запрос на каждую строку. Большие объёмы обрабатывают в staging одним запросом.
  • Блокирующие преобразования. Sort и Aggregate ждут весь поток и держат его в памяти. Сортировку отдают источнику (ORDER BY и IsSorted на выходе).
  • Дата изменения как водяной знак. Время ставится до коммита, и строки долгой транзакции проскакивают.

Правило

Запуск безопасен для повтора: staging очищается в начале, перенос идёт по бизнес-ключу, водяной знак двигается только после успеха.

Под капотом

  • Data flow работает буферами в памяти. Синхронные преобразования (Derived Column, Lookup) меняют строки в том же буфере, асинхронные копируют их в новые.
  • OLE DB Destination в режиме fast load вставляет пакетами; Maximum insert commit size задаёт размер транзакции.
  • Checkpoints перезапускают пакет с упавшей задачи control flow, но не с середины data flow, а циклы Foreach выполняются заново. Идемпотентные шаги надёжнее.

Компромиссы

  • Change tracking синхронный: каждая запись платит за учёт. Если последняя синхронизация старше CHANGE_TRACKING_MIN_VALID_VERSION, нужна полная перезагрузка.
  • CDC асинхронный, но журнал не усекается, пока задание захвата его не обработало.
  • В SSIS 2025 удалены CDC-компоненты Attunity и Microsoft Connector for Oracle, а 32-битный режим объявлен устаревшим. Для новых загрузок Microsoft предлагает Azure Data Factory и Data Factory в Fabric; готовые пакеты запускают в ADF на Azure-SSIS Integration Runtime.
  • Загрузку на .NET (SqlBulkCopy в staging, процедура с MERGE) проще тестировать и версионировать, но логирование, перезапуск и параллелизм пишутся вручную.

Как проверить в проде

  • Каталог SSISDB хранит историю запусков: catalog.executions и catalog.event_messages, а с уровнем логирования Performance — время компонентов в catalog.execution_component_phases.
  • После загрузки сверяют количество строк и суммы источника и цели.

Итог: надёжный ETL — это инкремент без потерь, идемпотентный перенос и наблюдаемость; инструмент вторичен.

T-SQL
SET XACT_ABORT ON;


DECLARE @from binary(8) = (SELECT LastVersion FROM etl.Watermark WHERE TableName = N'Sale');
-- Верхняя граница: строки незавершённых транзакций дождутся следующего запуска
DECLARE @to binary(8) = MIN_ACTIVE_ROWVERSION();


-- Extract: в SSIS это data flow из источника в staging
TRUNCATE TABLE stg.Sale;
INSERT INTO stg.Sale (SaleId, StoreId, Amount)
SELECT SaleId, StoreId, Amount
FROM src.Sale
WHERE RowVer >= @from AND RowVer < @to;


-- Load: повторный запуск не создаст дублей
BEGIN TRAN;


MERGE dw.Sale AS t
USING stg.Sale AS s ON t.SaleId = s.SaleId
WHEN MATCHED THEN UPDATE SET t.StoreId = s.StoreId, t.Amount = s.Amount
WHEN NOT MATCHED THEN INSERT (SaleId, StoreId, Amount) VALUES (s.SaleId, s.StoreId, s.Amount);


-- Водяной знак двигается вместе с данными
UPDATE etl.Watermark SET LastVersion = @to WHERE TableName = N'Sale';


COMMIT;

Если @@DBTS взять вместо MIN_ACTIVE_ROWVERSION(), строка из транзакции, которая закоммитится позже, получит версию ниже сохранённой границы и не загрузится никогда.

Источники
Middle+

Как построить отчётность, чтобы отчёты не положили рабочую базу?

Заголовок раздела «Как построить отчётность, чтобы отчёты не положили рабочую базу?»
Ответ

Что это

OLTP OLAP
Запросы короткие, по ключу агрегаты по миллионам строк
Пример продажа на кассе выручка по магазинам за год
Схема нормализованная денормализованная, «звезда»

Зачем разделять

Тяжёлый отчёт занимает процессор и диск рабочей базы и может блокировать запись. Кассы и сайт начинают ждать.

Как пользоваться

  • Отчёты строят в SSRS: отчёт описан в файле RDL, сервер отчётов выполняет запрос и отдаёт результат в браузер, Excel или PDF.
  • Тяжёлые отчёты подключают не к рабочей базе, а к её копии или к хранилищу данных.

Частая ошибка

Лечить блокировки отчётов подсказкой NOLOCK. Отчёт начинает читать незакоммиченные данные и может пропустить или дважды посчитать строки.

Как устроено

Отчёт мешает рабочей базе тремя путями:

  1. Ресурсы. Сканы и сортировки занимают CPU, диск и память под запросы.
  2. Кеш. Прочитанные страницы вытесняют из буферного пула горячие данные.
  3. Блокировки. Под READ COMMITTED чтение ставит разделяемые блокировки и конфликтует с записью.

Когда что выбирать

Решение Задержка данных Когда
RCSI на рабочей базе нет отчёты лёгкие, мешают только блокировки
Кеш и снимки SSRS минуты или сутки один отчёт открывают многие
Реплика Always On секунды оперативные отчёты, Enterprise
Витрина данных до следующего ETL аналитика, история, тяжёлые агрегаты

Ловушки

  • Реплика — та же схема. Индекс под отчёт создают на первичной базе, и он замедляет запись. Свои индексы возможны только в витрине или в базе-подписчике репликации.
  • Параметры без границ. Период «за всё время» по умолчанию превращает отчёт в полный скан.
  • Подписки в одно время. Сотни рассылок в 9:00 — пик нагрузки, который легко размазать.

Правило

Сначала найти тяжёлые отчёты, затем уводить их по допустимой задержке: оперативные — на реплику, аналитику — в витрину.

Под капотом

  • Читаемая реплика применяет журнал потоком redo. Все запросы на ней идут под snapshot isolation, подсказки блокировок игнорируются.
  • Платит первичная база: при читаемой реплике к изменённым строкам добавляются 14 байт под версии, что может вызвать разделение страниц.
  • Долгий отчёт на реплике держит Sch-S и может задержать redo DDL и очистку ghost-записей на первичной.
  • Columnstore хранит данные по колонкам со сжатием и обрабатывает пакетами строк. С SQL Server 2016 некластерный columnstore на рабочей таблице обновляемый — это real-time operational analytics.

Компромиссы

Вариант Цена
Реплика Always On Enterprise; схема и индексы как у первичной; лаг растёт под нагрузкой
Columnstore на рабочей таблице дороже каждая запись, место под индекс
Витрина со звездой ETL, задержка, ещё одна модель данных

Basic availability group в Standard реплику для чтения не даёт.

Отчётный сервер в 2025+

SSRS 2022 — последняя версия, поддержка до 11 января 2033 года. С SQL Server 2025 локальная отчётность — Power BI Report Server: те же RDL-отчёты плюс интерактивные PBIX, право на него входит в лицензии Enterprise и Standard. Облачный путь — Power BI, где RDL работают как paginated reports.

Как проверить в проде

  • ExecutionLog3 в базе сервера отчётов показывает по каждому запуску время получения данных, обработки и рендеринга.
  • Отставание реплики — sys.dm_hadr_database_replica_states: log_send_queue_size, redo_queue_size.

Итог: отчётность разводят с транзакциями по допустимой задержке, а платформу выбирают с учётом конца линейки SSRS.

T-SQL
-- База сервера отчётов: по умолчанию ReportServer, журнал хранится 60 дней
USE ReportServer;


-- Какие отчёты дольше всех ждали базу за неделю
SELECT TOP (10)
    ItemPath,
    COUNT(*)                                       AS Runs,
    SUM(CAST(TimeDataRetrieval AS bigint)) / 1000  AS DataSecTotal,
    AVG(CAST(TimeDataRetrieval AS bigint)) / 1000  AS DataSecAvg,
    MAX([RowCount])                                AS MaxRows
FROM dbo.ExecutionLog3
WHERE TimeStart >= DATEADD(DAY, -7, SYSDATETIME())
GROUP BY ItemPath
ORDER BY DataSecTotal DESC;
T-SQL
-- Оперативная аналитика прямо по рабочей таблице (SQL Server 2016+):
-- транзакции пишут в обычный индекс, агрегаты читают сжатые колонки
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Sale
    ON dbo.Sale (StoreId, SoldAt, Amount);


SELECT StoreId, SUM(Amount) AS Revenue
FROM dbo.Sale
WHERE SoldAt >= '2026-10-01'
GROUP BY StoreId;
Источники
Senior+

Как масштабировать базу: партиционирование, реплики, шардирование и архив?

Заголовок раздела «Как масштабировать базу: партиционирование, реплики, шардирование и архив?»
Ответ

Что это

  • Партиционирование — таблица хранится секциями по значению колонки, например по месяцу продажи. Для запросов это одна таблица.
  • Реплика для чтения — копия базы, на которую уводят отчёты.
  • Шардирование — данные разных магазинов или клиентов лежат в разных базах на разных серверах.
  • Архивирование — старые данные переносят из рабочей таблицы в архив.

Зачем

Большую таблицу дольше обслуживать, копировать и чистить. Удалить прошлый год через DELETE — часы блокировок и гигабайты журнала, а с партиционированием это переключение секции.

Частая ошибка

Ждать, что партиционирование само ускорит запросы. Если в условии нет колонки секционирования, запрос читает все секции и может стать медленнее.

Как устроено партиционирование

  1. Функция секционирования задаёт границы: RANGE RIGHT FOR VALUES ('2026-09-01', '2026-10-01').
  2. Схема секционирования раскладывает секции по файловым группам.
  3. Таблицу и индексы создают на схеме. Индекс на той же схеме — выровненный; SWITCH работает только с выровненными индексами.

Скользящее окно для архива

  1. SPLIT RANGE добавляет пустую секцию под следующий месяц.
  2. SWITCH PARTITION переносит старейший месяц в архивную таблицу той же структуры.
  3. MERGE RANGE убирает опустевшую границу.

Когда что выбирать

Проблема Решение
Таблицу долго обслуживать и чистить партиционирование
Чтение перегружает сервер реплики для чтения
Запись не помещается в один сервер шардирование

Ловушки

  • Уникальный индекс включает колонку секционирования, иначе он не выровнен. Отсюда составной ключ (SaleId, SoldOn).
  • Запрос без ключа секционирования ищет в каждой секции. Microsoft прямо пишет, что в OLTP партиционирование редко ускоряет запросы.
  • SPLIT и MERGE непустой секции перемещают строки и пишут журнал, поэтому крайние секции держат пустыми.

Под капотом

  • SWITCH меняет принадлежность страниц, данные не копируются. Условия: одинаковые колонки и индексы, та же файловая группа, цель пуста; при загрузке в секцию источник подтверждает диапазон CHECK-ограничением.
  • SWITCH нужна монопольная блокировка обеих таблиц. С SQL Server 2014 WAIT_AT_LOW_PRIORITY не даёт ей встать в очередь перед всеми запросами.
  • TRUNCATE TABLE ... WITH (PARTITIONS (2)) (SQL Server 2016+) очищает секцию без архива. Партиционирование есть во всех редакциях с SQL Server 2016 SP1.

Цена

  • До 15 000 секций, но тысячи секций стоят памяти, а запросы без исключения секций замедляются.
  • Читаемые реплики — только Enterprise; basic availability group в Standard не даёт читать со вторичной.

Компромиссы шардирования

Что теряем Чем платим
Соединения и агрегаты между шардами запросы ко всем шардам, отчёты через хранилище
Транзакции между шардами саги, outbox, идемпотентность
Глобальная уникальность GUID или ключ с номером шарда
Равномерность перебалансировка, «горячие» шарды

Справочники вроде товаров и магазинов копируют в каждый шард.

Как проверить в проде

  • sys.dm_db_partition_stats показывает строки по секциям, а план — Actual Partition Count.
  • Отставание реплик — sys.dm_hadr_database_replica_states.

Итог: один сервер выжимают до конца, партиционируют ради обслуживания, а шардируют, только когда запись упёрлась в сервер и есть естественный ключ раздела.

T-SQL
-- RANGE RIGHT: граница — первое число месяца; крайние секции держим пустыми
CREATE PARTITION FUNCTION pfMonth (date)
    AS RANGE RIGHT FOR VALUES ('2026-08-01', '2026-09-01', '2026-10-01', '2026-11-01');
CREATE PARTITION SCHEME psMonth AS PARTITION pfMonth ALL TO ([PRIMARY]);


CREATE TABLE dbo.Sale (
    SaleId bigint        NOT NULL,
    SoldOn date          NOT NULL,
    Amount decimal(12,2) NOT NULL,
    CONSTRAINT PK_Sale PRIMARY KEY (SaleId, SoldOn) -- ключ включает колонку секционирования
) ON psMonth (SoldOn);


-- Архив: та же структура и файловая группа, пустой
CREATE TABLE dbo.SaleArchive (
    SaleId bigint NOT NULL, SoldOn date NOT NULL, Amount decimal(12,2) NOT NULL,
    CONSTRAINT PK_SaleArchive PRIMARY KEY (SaleId, SoldOn)
) ON [PRIMARY];


-- 1. Пустая секция под декабрь, пока данных за ноябрь ещё нет
ALTER PARTITION SCHEME psMonth NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION pfMonth() SPLIT RANGE ('2026-12-01');


-- 2. Август (секция 2) уходит в архив: меняются только метаданные
ALTER TABLE dbo.Sale SWITCH PARTITION 2 TO dbo.SaleArchive
    WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF));


-- 3. Убрать границу между двумя пустыми секциями
ALTER PARTITION FUNCTION pfMonth() MERGE RANGE ('2026-08-01');
Источники
Senior+

Как менять схему базы без простоя?

Заголовок раздела «Как менять схему базы без простоя?»
Ответ

Что это

Изменение схемы без простоя — база меняется, пока приложение работает и магазины продолжают продавать.

Как пользоваться

Переименование колонки Phone в PhoneNumber по шагам:

  1. Добавить колонку PhoneNumber, разрешив NULL. Старое приложение её не замечает.
  2. Выпустить версию приложения, которая пишет в обе колонки.
  3. Скопировать старые данные небольшими пакетами.
  4. Выпустить версию, которая читает только PhoneNumber.
  5. Удалить Phone, когда старых версий приложения не осталось.

Частая ошибка

Переименовать или удалить колонку в одном релизе с кодом. Пока обновлены не все серверы, старая версия обращается к колонке, которой уже нет, и падает.

Как устроено

Expand/contract — три фазы:

  1. Expand. Только совместимые изменения: новая nullable-колонка, таблица, индекс. Старый код работает.
  2. Migrate. Код пишет в старое и новое, backfill переносит историю, чтение переключается на новое.
  3. Contract. Когда старого кода нет и окно отката прошло, старое удаляют.

Сколько держится блокировка

Операция Как выполняется
Новая nullable-колонка метаданные, мгновенно
NOT NULL с константным default метаданные в Enterprise (2012+), иначе перезапись строк
Смена типа, например int → bigint перезапись всех строк под Sch-M
Индекс с ONLINE = ON онлайн, короткие блокировки в начале и конце; Enterprise

Ловушки

  • Очередь за Sch-M. ALTER ждёт долгий отчёт, а все новые запросы к таблице ждут ALTER. Перед DDL ставят SET LOCK_TIMEOUT и повторяют попытку.
  • Один большой UPDATE. Backfill одной транзакцией раздувает журнал, вызывает эскалацию блокировок до таблицы и задерживает реплики. Нужны пакеты по несколько тысяч строк.
  • Переименование в один шаг. sp_rename колонки, которую читает работающая версия, — ломающее изменение.

Правило

Каждая миграция совместима с текущей и предыдущей версией приложения.

Под капотом

  • Sch-M несовместима ни с одной блокировкой, даже со стабильностью схемы у читателей. Важно, сколько DDL её держит и сколько ждёт её получения.
  • Эскалация срабатывает примерно на 5 000 блокировках одного оператора на одном объекте. Пакеты меньше этого порога держат построчные блокировки и не останавливают остальных.
  • ADR (SQL Server 2019+) делает откат долгой транзакции мгновенным и усекает журнал во время неё; без ADR отмена часового UPDATE может длиться столько же.

Смена типа на большой таблице

  1. Добавить колонку нового типа, nullable.
  2. Синхронизировать новые изменения — кодом или триггером.
  3. Backfill пакетами по диапазонам ключа.
  4. Переключить чтение и запись, затем удалить старую колонку.

ALTER COLUMN ... WITH (ONLINE = ON) (SQL Server 2016+, Enterprise) оставляет таблицу доступной, но строки всё равно переписываются.

Компромиссы

  • State-based развёртывание (dacpac, sqlpackage) строит скрипт сравнением схем и при некоторых изменениях пересоздаёт таблицу копированием, поэтому скрипт читают до прода. Миграции (EF Core, DbUp) явны, но порядок шагов на вас.
  • После expand откатывают только код: схема совместима со старой версией. Обратные миграции с потерей данных на проде заменяют исправлением вперёд.
  • Триггер синхронизации ловит всех писателей, включая ETL, но удорожает каждую запись.

Как проверить в проде

  • Во время backfill следят за log_reuse_wait_desc в sys.databases и redo_queue_size реплик; пауза между пакетами — регулятор.
  • Очередь за DDL видна в sys.dm_exec_requests: wait_type LCK_M_SCH_M и blocking_session_id.

Итог: без простоя — это совместимость с двумя версиями кода, короткие блокировки и управляемая очередь за Sch-M.

T-SQL
-- DDL ждёт блокировку не дольше 5 секунд и не держит очередь за собой
SET LOCK_TIMEOUT 5000;


-- Expand: новая nullable-колонка шире старой — только метаданные
ALTER TABLE dbo.Receipt ADD TotalAmount decimal(14,2) NULL;


-- Backfill пакетами по диапазону ключа: короткие транзакции без эскалации
DECLARE @batch bigint = 4000, @from bigint = 0, @max bigint;
SELECT @max = MAX(ReceiptId) FROM dbo.Receipt;


WHILE @from < @max
BEGIN
    UPDATE dbo.Receipt
    SET TotalAmount = Total
    WHERE ReceiptId > @from AND ReceiptId <= @from + @batch
      AND TotalAmount IS NULL; -- повторный запуск продолжит с места остановки


    SET @from += @batch;
    WAITFOR DELAY '00:00:00.100'; -- место для рабочей нагрузки и реплик
END;


-- Contract — отдельным релизом, когда код больше не читает старую колонку
-- ALTER TABLE dbo.Receipt DROP COLUMN Total;

Пока идёт backfill, новая версия приложения пишет в обе колонки, поэтому новые чеки уже заполнены.

Источники