I have a table using Microsoft SQL Server 2012
Name
+-----------+------------+
| IDName | Name |
+-----------+------------+
|1 | Doddy |
|2 | Rinta |
|3 | Krida |
|4 | Debbie |
+-----------+------------+
I set the "IDName" column and set "Yes" in Identity Spesification (Is Identity), Identity increment = 1 and identity seed = 1. So, if i insert new row, for example "Bobby" and "Tommy", IDName column is inserted automatically
Name
+-----------+------------+
| IDName | Name |
+-----------+------------+
|1 | Doddy |
|2 | Rinta |
|3 | Krida |
|4 | Debbie |
|5 | Bobby |
|6 | Tommy |
+-----------+------------+
If i delete a row, the table will be like this
Name
+-----------+------------+
| IDName | Name |
+-----------+------------+
|1 | Doddy |
|2 | Rinta |
|3 | Krida |
|4 | Debbie |
|5 | Bobby |
+-----------+------------+
Now is my problem. If i add a new row,with the same or different name, the IDName is not set to 6, but 7 like this.
Name
+-----------+------------+
| IDName | Name |
+-----------+------------+
|1 | Doddy |
|2 | Rinta |
|3 | Krida |
|4 | Debbie |
|5 | Bobby |
|7 | Tommy |
+-----------+------------+
If i re-delete and re-insert, IDName is not set to 6 or 7, but 8.
Name
+-----------+------------+
| IDName | Name |
+-----------+------------+
|1 | Doddy |
|2 | Rinta |
|3 | Krida |
|4 | Debbie |
|5 | Bobby |
|8 | Tommy |
+-----------+------------+
How can i fix it? I've tried but nothing is successful. Thank you.
Better you try to write a procedure to insert.
Before inserting get the max IDName from the table and insert that IDName+1 into the table.
That's a workaround. It will be helpful if you are facing problem only in deletion.