SQL Server: insert an explicit ID and reset identity (auto increment)

To insert a row with a specific value in an IDENTITY column, for example to fill the gap left by a deleted row:

SET IDENTITY_INSERT dbo.Tool ON;

-- The column list is required
INSERT INTO dbo.Tool (ID, Name)
VALUES (3, 'Garden shovel');

SET IDENTITY_INSERT dbo.Tool OFF;

To start numbering from 1 again after deleting every row with DELETE (for IDENTITY(1,1)):

-- The table was emptied with DELETE: the next ID is 0 + 1
DBCC CHECKIDENT ('dbo.Tool', RESEED, 0);

If the table was emptied with TRUNCATE TABLE, the same command gives the next row ID 0. Details below.

IDENTITY_INSERT rules

• Without a column list the insert fails with error 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. With a column list but IDENTITY_INSERT off, with error 544: Cannot insert explicit value for identity column in table 'Tool' when IDENTITY_INSERT is set to OFF.
• Only one table in a session can have IDENTITY_INSERT on. Turning it on for a second table fails with an error that names the table where it's already on.
• If you insert a value larger than the current identity, SQL Server moves the counter up: after inserting ID 1000 by hand, the next automatic ID is 1001.
• You need to own the table or have ALTER permission on it.

DBCC CHECKIDENT RESEED: 0 or 1

After DBCC CHECKIDENT ('dbo.Tool', RESEED, N) the next row gets:

The table	Next ID
never had rows, or was emptied with TRUNCATE TABLE	N
has rows, or was emptied with DELETE	N + increment

A rolled-back insert counts as a deleted row: the next ID is N + increment.

After TRUNCATE TABLE you don't need CHECKIDENT at all: TRUNCATE already resets the counter to the seed from the table definition.

Check and fix the counter

-- Current counter and the largest ID in the column; changes nothing
DBCC CHECKIDENT ('dbo.Tool', NORESEED);

-- The current counter as a value
SELECT IDENT_CURRENT('dbo.Tool');

-- If the counter is below the largest ID, raise it to the largest ID
DBCC CHECKIDENT ('dbo.Tool', RESEED);

RESEED without a number never lowers the counter. After deleting the last rows, lower it in two steps: set a small value, then let RESEED raise it to the largest ID left:

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

Don't set the counter below the largest ID in a table with rows. With a PRIMARY KEY or UNIQUE constraint, later inserts fail with error 2627. Without one, you get duplicate IDs.

To run DBCC CHECKIDENT you must own the schema that contains the table or be a member of sysadmin, db_owner or db_ddladmin.

What doesn't work

• UPDATE dbo.Tool SET ID = 5 WHERE ID = 3 fails with Cannot update identity column 'ID'. To change an ID, insert a copy of the row with IDENTITY_INSERT and delete the old one.
• CHECKIDENT doesn't renumber existing rows and doesn't change the seed from the table definition. For that, recreate the identity column.
• Gaps are normal: rolled-back transactions and server restarts skip values. SQL Server 2017 and later can turn off the identity cache behind restart gaps with ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF;, but if you need numbers without gaps, keep them in a separate column.