SQL Server: вставка с явным ID и сброс автоинкремента (IDENTITY)

Чтобы вставить строку с конкретным значением в столбец IDENTITY, например заполнить дыру после удалённой строки:

SET IDENTITY_INSERT dbo.Tool ON;

-- Список столбцов обязателен
INSERT INTO dbo.Tool (ID, Name)
VALUES (3, 'Garden shovel');

SET IDENTITY_INSERT dbo.Tool OFF;

Чтобы после удаления всех строк через DELETE нумерация снова началась с 1 (для IDENTITY(1,1)):

-- Таблицу очистили через DELETE: следующий ID будет 0 + 1
DBCC CHECKIDENT ('dbo.Tool', RESEED, 0);

Если таблицу очистили через TRUNCATE TABLE, та же команда даст следующей строке ID 0. Подробности ниже.

Правила IDENTITY_INSERT

• Без списка столбцов вставка падает с ошибкой 8101: An explicit value for the identity column in table 'MyDb.dbo.Tool' can only be specified when a column list is used and IDENTITY_INSERT is ON. Со списком, но при выключенном IDENTITY_INSERT — с ошибкой 544: Cannot insert explicit value for identity column in table 'Tool' when IDENTITY_INSERT is set to OFF.
• В одном сеансе IDENTITY_INSERT включается только для одной таблицы. Если включить его для второй, SQL Server ответит ошибкой и назовёт таблицу, где он уже включён.
• Если вставить значение больше текущего счётчика, SQL Server передвинет счётчик: после ручной вставки ID 1000 следующий автоматический ID будет 1001.
• Нужно быть владельцем таблицы или иметь на неё право ALTER.

DBCC CHECKIDENT RESEED: 0 или 1

После DBCC CHECKIDENT ('dbo.Tool', RESEED, N) следующая строка получит:

Таблица	Следующий ID
в неё ещё ни разу не вставляли строки или её очистили через TRUNCATE TABLE	N
в ней есть строки или её очистили через DELETE	N + шаг

Откаченная вставка считается удалённой строкой: следующий ID будет N + шаг.

После TRUNCATE TABLE команда CHECKIDENT не нужна: TRUNCATE сам сбрасывает счётчик к начальному значению из определения таблицы.

Проверить и поправить счётчик

-- Текущий счётчик и наибольший ID в столбце; ничего не меняет
DBCC CHECKIDENT ('dbo.Tool', NORESEED);

-- Текущее значение счётчика
SELECT IDENT_CURRENT('dbo.Tool');

-- Если счётчик меньше наибольшего ID, поднять его до наибольшего ID
DBCC CHECKIDENT ('dbo.Tool', RESEED);

RESEED без числа счётчик не уменьшает. После удаления последних строк уменьшите его в два шага: поставьте маленькое значение, потом RESEED поднимет его до наибольшего оставшегося ID:

DBCC CHECKIDENT ('dbo.Tool', RESEED, 0);
DBCC CHECKIDENT ('dbo.Tool', RESEED);

Не ставьте счётчик ниже наибольшего ID в таблице со строками. С ограничением PRIMARY KEY или UNIQUE следующие вставки упадут с ошибкой 2627. Без ограничения появятся повторяющиеся ID.

Для DBCC CHECKIDENT нужно быть владельцем схемы или состоять в sysadmin, db_owner или db_ddladmin.

Что не сработает

• UPDATE dbo.Tool SET ID = 5 WHERE ID = 3 падает с Cannot update identity column 'ID'. Чтобы поменять ID, вставьте копию строки через IDENTITY_INSERT и удалите старую.
• CHECKIDENT не перенумеровывает существующие строки и не меняет начальное значение из определения таблицы. Для этого придётся пересоздать столбец.
• Дыры в нумерации — норма: откаченные транзакции и перезапуски сервера пропускают значения. В SQL Server 2017 и новее кеш, из-за которого номера прыгают после перезапуска, выключается командой ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF;. Но если нужны номера без пропусков, храните их в отдельном столбце.