Overriding Identity Fields: Put a Deleted Row Back With Its Old ID

Overriding identity fields is rare, but one case needs it: putting a deleted row back with its old ID. An identity column numbers new rows for you, and it refuses any number you supply. A setting called IDENTITY_INSERT lifts that rule for one table at a time.

The Sample Tables

Two tables, both with an identity column. Workshops holds five rows. Signups is only there to test a rule later. Run this in any test database. Every result in this post comes from SQL Server 2025.

CREATE TABLE Workshops (WorkshopID INT IDENTITY(1,1) PRIMARY KEY, Title VARCHAR(30) NOT NULL, Seats INT NOT NULL);
CREATE TABLE Signups (SignupID INT IDENTITY(1,1) PRIMARY KEY, WorkshopID INT NOT NULL, Name VARCHAR(20) NOT NULL);
INSERT INTO Workshops (Title, Seats) VALUES ('Pottery Basics', 12), ('Herb Garden', 10), ('Bread Baking', 8), ('Watercolor Walk', 15), ('Tea Tasting', 20);
GO

SELECT WorkshopID, Title, Seats FROM Workshops ORDER BY WorkshopID;

Identity Never Reuses a Number

An insert that names the ID fails while IDENTITY_INSERT is off, which is the default.

INSERT INTO Workshops (WorkshopID, Title, Seats) VALUES (6, 'Seed Swap', 25);

SQL Server answers with Msg 544. Now delete workshops 4 and 5, then insert a new workshop the normal way.

DELETE FROM Workshops WHERE WorkshopID IN (4, 5);

INSERT INTO Workshops (Title, Seats) VALUES ('Kite Making', 14);
SELECT WorkshopID, Title FROM Workshops WHERE Title = 'Kite Making';

The new row gets ID 6. There is no 4 or 5 now, and there won’t be until you put them back. Identity moves forward only.

Turn IDENTITY_INSERT On

The setting is per table and per session. First, try the shortcut: turn it on and insert by position, without naming the columns.

SET IDENTITY_INSERT Workshops ON;
INSERT INTO Workshops VALUES (4, 'Watercolor Walk', 15);

That returns Msg 8101. A reader asked what “by name” and “by position” mean. By position means INSERT INTO Workshops VALUES (…), where SQL Server matches values to columns in table order. By name means listing the columns, as in INSERT INTO Workshops (WorkshopID, Title, Seats). An explicit identity value needs the second form.

The failed insert left the setting on. Turn it off, then run the correct version.

SET IDENTITY_INSERT Workshops OFF;

SET IDENTITY_INSERT Workshops ON;
INSERT INTO Workshops (WorkshopID, Title, Seats) VALUES (4, 'Watercolor Walk', 15), (5, 'Tea Tasting', 20);
SET IDENTITY_INSERT Workshops OFF;

SELECT WorkshopID, Title, Seats FROM Workshops ORDER BY WorkshopID;

All six rows are there, in ID order, and 4 and 5 hold their old values again. The last line of the restore turned the setting off. Do that every time, because a session that forgets it can block the next one.

INSERT INTO Workshops (Title, Seats) VALUES ('Lantern Craft', 18);
SELECT WorkshopID, Title FROM Workshops WHERE Title = 'Lantern Craft';

The new workshop gets ID 7. Restoring 4 and 5 didn’t move the counter back, because they were below it.

A Higher Number Moves the Counter

The opposite happens when you insert a value above the counter. SQL Server continues from the highest value it has seen.

SET IDENTITY_INSERT Workshops ON;
INSERT INTO Workshops (WorkshopID, Title, Seats) VALUES (100, 'Spring Fair', 40);
SET IDENTITY_INSERT Workshops OFF;
INSERT INTO Workshops (Title, Seats) VALUES ('After The Fair', 9);
SELECT WorkshopID, Title FROM Workshops WHERE WorkshopID > 7 ORDER BY WorkshopID;

The row with ID 100 comes first, and the next automatic row gets 101.

The Rules

Only one table per session can have the setting on. The second request fails until you turn the first one off.

SET IDENTITY_INSERT Workshops ON;
SET IDENTITY_INSERT Signups ON;

SET IDENTITY_INSERT Workshops OFF;

The error is Msg 8107. A second rule is that you can’t change an identity value with UPDATE.

UPDATE Workshops SET WorkshopID = 200 WHERE WorkshopID = 1;

That returns Msg 8102. To change an ID, insert the row again with the new value and delete the old one.

Identity doesn’t guarantee unique numbers by itself. With IDENTITY_INSERT on, an existing ID is refused only because the table has a primary key.

SET IDENTITY_INSERT Workshops ON;
INSERT INTO Workshops (WorkshopID, Title, Seats) VALUES (1, 'Copy Of Pottery', 12);
SET IDENTITY_INSERT Workshops OFF;

That returns Msg 2627, from the primary key. This table has no key, and it accepts the same ID twice.

CREATE TABLE Loose (LooseID INT IDENTITY(1,1), Note VARCHAR(10) NOT NULL);
INSERT INTO Loose (Note) VALUES ('first');
SET IDENTITY_INSERT Loose ON;
INSERT INTO Loose (LooseID, Note) VALUES (1, 'second');
SET IDENTITY_INSERT Loose OFF;
SELECT LooseID, Note FROM Loose ORDER BY Note;
DROP TABLE Loose;

The result holds two rows with LooseID 1. If IDs must be unique, add a primary key or a unique constraint.

Should You Fill the Gap?

A reader pointed out that knowing how to do this is not a reason to do it. The reader is right. A gap in an identity column harms nothing, so don’t refill gaps to tidy the numbers. Restore an old ID when other rows point at it, such as signups that refer to a workshop. Restore it too when you reload rows from a backup.

Clean Up

Remove the sample tables when you finish.

DROP TABLE Signups;
DROP TABLE Workshops;

What to Remember

Turn IDENTITY_INSERT on for one table, insert with a column list, and turn it off again. Identity numbers only move forward, and a higher value moves the counter with it. Use a primary key to keep the IDs unique.

To go further, read Explicit Identity Values: Loading Rows With Their Old Keys, Gaps in an Identity Column: Find Missing Identity Values and Identity Jumping 1000 and IDENTITY_CACHE.

IDENTITY_INSERT is not a repair tool for tidy numbers, it is a way to restore what the rows point at.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.