IDENTITY SEED увеличивается на основе начальных значений других таблиц

Документация достаточно конкретна в отношении того, что identity не гарантирует:

Свойство identity в столбце не гарантирует следующее:

• Уникальность значения. . .

• Последовательные значения внутри транзакции. . .

• Последовательные значения после перезагрузки сервера или других сбоев. . .

• Повторное использование значений

В общем, свойство «уникальность» не является проблемой, потому что столбцы идентификаторов обычно являются первичным ключом (или обычно объявляются как минимум unique), что гарантирует уникальность.

Цель столбца identity — предоставить уникальный числовой идентификатор, отличный от других строк, поэтому его можно легко использовать в качестве первичного ключа. Других гарантий нет. А для повышения производительности SQL Server имеет множество сокращений, которые приводят к пробелам.

Если вы не хотите пробелов, то самый простой способ — присвоить значение при запросе:

row_number() over (order by <identity column>)

Это не на 100% удовлетворительно, потому что удаление может повлиять на значение. Я также думаю, что в параллельных системах вставки тоже могут (поскольку идентификаторы могут кэшироваться на отдельных узлах).

Если вас не волнует производительность, вы можете использовать sequence для присвоения значения. Это менее эффективно, чем использование identity. По сути, требуется сериализация всех вставок, чтобы гарантировать свойства вставки.

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