Have you ever encountered a situation where you delete rows from a table with an identity column, only to find that the new rows you insert start from the next identity value, creating a gap in the sequence? This can be a problem if your application requires all identities to be in sequence. In this article, we will discuss a simple solution to avoid gaps in identity columns in SQL Server.

Let’s consider a scenario where a developer had a table with an identity column and needed to delete a few rows. After deleting the rows and inserting new ones, they noticed that the identities started from the next value, creating a gap. This was not acceptable for their application.

The solution to this issue is to use two additional SQL tricks: reseeding the identity column. Let’s take a look at an example to understand how this works.

USE AdventureWorks

/* Create a table with one identity column */
CREATE TABLE TableID (
    ID INT IDENTITY (1, 1),
    Col VARCHAR(10)
)

/* Insert 10 records with the first value */
INSERT INTO TableID (Col) VALUES ('First')

/* Check the records in the table */
SELECT * FROM TableID

/* Delete the last few records */
DELETE FROM TableID WHERE ID IN (8, 9, 10)

/* Check the records in the table */
SELECT * FROM TableID

/* Get the current max value and reseed the table */
DECLARE @MaxID INT
SELECT @MaxID = MAX(ID) FROM TableID
DBCC CHECKIDENT ('TableID', RESEED, @MaxID)

/* Insert 10 records with the second value */
INSERT INTO TableID (Col) VALUES ('Second')

/* Check the records in the table */
SELECT * FROM TableID

/* Clean Database */
DROP TABLE TableID

In the above example, we create a table with an identity column and insert 10 records with the first value. We then delete the last few records, resulting in a gap in the identity column. To fix this, we use the DBCC CHECKIDENT command to reseed the table with the maximum identity value. Finally, we insert 5 records with the second value.

By following this approach, you can ensure that your identity column remains in sequence without any gaps. This can be particularly useful in scenarios where maintaining a continuous sequence is crucial for your application.

I hope this solution is clear to all my readers, and you can use it to avoid problems related to gaps in identity columns. If you have any feedback or need further explanation, please feel free to reach out to me.