Reset sequence or primary key after delete in SQL server

When you delete some rows from a table for whatever reason, there might be a gap in your ID or primary key upon further insertion of data.

You can reset this to start from a number of your choice, but you got to be careful about two things:

a. Make sure the sequence number you set to start from does not already exist and keep it greater than the max of the ID

b. Make sure if any foreign key associations are taken care of i.e, delete any associated foreign keys from corresponding tables whilst deleting the necessary rows.

Now let’s say you have the column ID :

ID : 1,2,3,4,5,6,7,8

Delete last four rows making it 1,2,3,4

Now when you insert new record it becomes: 1,2,3,4,9

Let’s delete the row with 9 as the primary key and any corresponding foreign keys.

1. Get max of the ID row and add 1:

(Which in our case is 4. The+1 is to start the ID sequence after max value)

declare @max_id as int = (SELECT MAX(your_column_name) FROM your_table)+1;

2. Restart your sequence:

exec(‘alter sequence your_column_name restart with ‘ + @max_id);

(Note: space after with is necessary)

Voila, now your sequence or ID becomes :

1,2,3,4,5!