Reset identity seed after deleting records in SQL Server

Identity management in SQL Server

Verify current identity seed with NORESEED

Should you want to check your current identity value without changing it, NORESEED comes to the rescue:

DBCC CHECKIDENT ('YourTable', NORESEED);


This command will fetch the current identity value without altering it, sparing you from potential aftermath of accidental modification.

Juggling with foreign key constraints

Avoid violating foreign key constraints when reseeding by explicitly removing and reinstating the constraints:

ALTER TABLE ChildTable DROP CONSTRAINT Constraint1;  --removes the constraint

-- For the love of data integrity, reseed safely
DBCC CHECKIDENT ('ParentTable', RESEED, 0);

ALTER TABLE ChildTable ADD CONSTRAINT Constraint1 FOREIGN KEY (ParentID) REFERENCES ParentTable(ID); --play nice, put it back


This ensures you won't experience referential integrity issues where identity column is used as a foreign key.

Reset identity with NULLs

Got rows with NULL values? Or maybe you've deleted a sizable number of rows? No worries!

DBCC CHECKIDENT ('CleaningTheNulls', RESEED, 0);


In these scenarios, the identity count is reset, prepping your table for the next non-null entry to boast a fresh identity.

Cleanse Table with TRUNCATE

Planning a full table reset, every programmer's spring cleaning?

TRUNCATE TABLE MessyTable;


TRUNCATE is the Hercules of SQL, stronger than DELETE — it deallocates data pages and renews the identity seed. As if your table went to a luxury SQL spa!

Dynamic reseed value

Compute the next identity

Instead of rigidly hard-coding, flexibly compute the next identity value. The data approves.

DECLARE @MaxID INT;  --Prepping the variable
SELECT @MaxID = ISNULL(MAX(ID), 0) FROM MaxTable;  --Getting the max existing ID
DBCC CHECKIDENT ('MaxTable', RESEED, @MaxID);  --Reseeding with the max ID, if you can't be the best, at least be the max


This approach tailors the identity sequence according to the highest current ID. Say no to gaps!

Cloud-based reseeding

Must manage identity in a cloud environment? Good news! DBCC CHECKIDENT is cloud-compatible.

-- Fear not, use the same command in Azure SQL Database
DBCC CHECKIDENT ('CloudTable', RESEED, 0);


Master of silence

Suppress output messages

Ever needed a clean output or running automated scripts? Don't get lost in the info-sauce!

DBCC CHECKIDENT ('QuietTable', RESEED, 0) WITH NO_INFOMSGS;


This magician's secret word, WITH NO_INFOMSGS, suppresses all informational noise to give you a pristine output.