Interview Question of the Week #019 – How to Reset Identity of Table

A reader emailed me after archiving and deleting old rows from a table. The table still had an identity column, and the reader wanted the next inserted row to start at one again.

Question: How do you reset a SQL Server identity so the next insert uses 1?

Answer: If the table was emptied with DELETE, the identity is IDENTITY(1,1), and reusing values is safe, reseed it to 0. SQL Server adds the increment for the next inserted row:

DBCC CHECKIDENT ('dbo.YourTable', RESEED, 0);
-- After DELETE removed every row, the next generated ID is 1.

That last condition matters. If the table was emptied with TRUNCATE TABLE, or it has never had a row, the first insert after an explicit reseed uses the reseed value itself. In that case, reseeding to 1 gives a first value of 1. Do not copy the 0 command into every empty-table scenario.

Never reseed below identity values still in the table. A primary key or unique index will reject a later collision; without one, the identity property alone does not guarantee uniqueness. Also check whether archived rows or other systems still refer to the old IDs before deliberately reusing them.

I made a short video showing the reset. The main interview point is to ask how the rows were removed before naming the reseed value.

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