Use case: you have an SQL Server database table and use Identity (auto increment) for the primary key (let’s be creative and call it id). For any reason, the table content is as following:

id	city
1	Hamburg
2	Berlin
3	Munich
4	Paris
78	London

Most of the time and technically, the out-of-sequence id 78 is not a big deal and your application still works as it should be. However, you are a perfectionist and don’t like to see the id number not being in a correct order. So you decided to fix it (RESEED), so long as the table is still small.

First check the identity property, with this command:

/* Check the current identity */
 dbcc checkident ('dbo.my_city')

Checking identity information: current identity value ’78’, current column value ’78’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Copy the affected record(s) into another table. It could be in a temporary table or in another object. I like to keep things simple:

/* copy the affected record(s) into another table */
 SELECT * INTO #my_city
 FROM dbo.my_city WHERE id = 78

Delete the affected record(s) from the origin table (in the live/productive environment, the DELETE is not an option most of the time. In this case, you have to take another approach, i.e. re-create the origin table).

/* delete the affected record(s) from the origin table */
 DELETE FROM dbo.mycity WHERE id = 78

Reseed the identity (the id 4 is the biggest number still in the correct order):

/* reseed the identity */
 dbcc checkident ('dbo.my_city', RESEED, 4)

And finally, copy the records back onto the origin table.

/* copy the affected record(s) back onto the origin table */
 INSERT INTO dbo.mycity (id, city)
 SELECT id, city FROM #my_city

You will then see, that London has an id of 5.

The complete code can be found here.

---

Photo by Roman Synkevych on Unsplash