Update Sequence After Deleting Table Row

Often we have an autoincrement column in a table where next insert into the table will be one more than the previous one. In SQL Server we can have an identity property on a column which can help in generating value based on current seed & increment. But in many cases we need to have auto increment depending on another column. Say for example in below image we have books for different languages and we have sequence number based on Book Type. 

Now if we delete a row from the table, the sequence number of subsequent rows will not change or get updated which will create a gap between two sequence number. This is one of the most common issues faced by developers and one of the most common question in various forums.

Solution 1

When we delete a row from the table, we can update the sequence number of subsequent rows by decrementing them by 1.

DELETE FROM BookDetail WHERE BookID=5

UPDATE BookDetail SET SequenceNo = SequenceNo - 1 WHERE BookID > 5

But decrementing by 1 will not work when we have deleted multiple row. For example 

DELETE FROM BookDetail WHERE SequenceNo <= 3

Solution 2

When we have update multiple rows in SQL with auto increment sequence, we can use Local Variable in SQL Server which will get incremented on each update. 

DECLARE @SequenceNumber INT = 0

UPDATE BookDetail SET @SequenceNumber = @SequenceNumber + 1, SequenceNo = @SequenceNumber

WHERE BookType = '.NET'

In this example, we have declared a SequenceNumber variable which will get incremented for each row, and we set that number in SequenceNo. Based on where condition in query, sequence number will get updated, and we will have sequence numbers without any gap.