Почему Identity и Sequence в SQL Server оставляют пропуски

Пропущенное значение identity не доказывает, что кто-то удалил строку. SQL Server может выдать номер вставке, которая позже откатится, и выдача номера не отменяется вместе с данными. Если считать identity счётчиком без пропусков, возникают ложные тревоги и опасные попытки повторного использования идентификаторов.

Технический ключ отвечает на вопрос, какая это строка. Бизнес-номер может обозначать позицию выпущенного документа в отдельном процессе. У этих требований разные жизненные циклы. Их разделение не позволяет особенностям хранения незаметно определить правила нумерации бизнеса.

Выдача номера не равна подтверждению

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

CREATE TABLE #Tickets (
    TicketId int IDENTITY(1,1) PRIMARY KEY,
    Note nvarchar(80) NOT NULL
);
INSERT #Tickets(Note) VALUES (N'First committed row');
BEGIN TRANSACTION;
INSERT #Tickets(Note) VALUES (N'This row is rolled back');
ROLLBACK TRANSACTION;
INSERT #Tickets(Note) VALUES (N'Next committed row');
SELECT TicketId, Note FROM #Tickets ORDER BY TicketId;
DROP TABLE #Tickets;

В результате остаются идентификаторы 1 и 3. Значение 2 получила отменённая вставка. Подтверждённые данные не потеряны: транзакция правильно убрала строку, а механизм выдачи продолжил работу.

IDENTITY относится к одной таблице. SEQUENCE является отдельным объектом схемы, значения которого можно запросить до вставки и использовать в разных таблицах. Это удобно, когда ключ нужен заранее, но неиспользованные номера становятся нормальной ситуацией. Значения последовательности также не возвращаются при откате транзакции.

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

Генерацию следует отделять от ограничения уникальности. Создайте первичный либо уникальный ключ для сохранённого идентификатора. Переустановка начального значения, явные identity-вставки и циклические последовательности иначе способны вызвать совпадения. Такие действия требуют управляемой миграции, а не регулярного исправления пропусков.

Получайте реально созданные значения

MAX(Id) плюс один не является безопасным предсказанием. Другой сеанс может вставить строку между чтением и записью. Разница между максимальным идентификатором и количеством строк также не показывает точное число удалений.

Для одиночной вставки SCOPE_IDENTITY возвращает последнюю identity текущей области выполнения. @@IDENTITY может вернуть значение, созданное триггером в другой области. Для нескольких строк OUTPUT inserted.Id выдаёт фактические ключи, но порядок результата нельзя автоматически сопоставлять порядку входных записей. Храните явный признак соответствия.

Полученные через OUTPUT значения не доказывают окончательный COMMIT всей транзакции. Более поздняя ошибка или откат способны отменить запись. Внешние уведомления должны следовать за подтверждённым бизнес-результатом и учитывать неопределённость при потере соединения.

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

Планируйте диапазон и бизнес-номера отдельно

Запрос показывает identity-столбцы и последние выданные значения.

SELECT OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
       OBJECT_NAME(object_id) AS TableName,
       name AS ColumnName,
       TYPE_NAME(user_type_id) AS DataType,
       seed_value, increment_value, last_value
FROM sys.identity_columns
ORDER BY SchemaName, TableName;

Оценивайте остаток диапазона с учётом типа, начального значения, направления шага и скорости выдачи. У положительного int есть конечный предел, а неудачные вставки тоже расходуют номера. Удаление старых строк этот предел не отдаляет.

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

Если бизнес требует контролируемой последовательности документов, назначайте отдельный номер на нужном этапе выпуска и определите представление отмен. Транзакционный счётчик может сериализовать выдачу и ограничить пропускную способность. Разделение по арендаторам, видам документов или периодам допустимо только при соответствующем бизнес-правиле.

Проверьте конкурентный выпуск, отмену, откат и повтор около COMMIT. Полезная гарантия заключается в объяснимой и соблюдаемой политике нумерации, а не во внешне последовательных значениях технического столбца.

Техническая документация: Microsoft Learn: IDENTITY property · Microsoft Learn: CREATE SEQUENCE · Microsoft Learn: OUTPUT clause.