-3

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.

Aji Saputra Raka Siwi
  • 358
  • 1
  • 12
  • 2
    This is the normal behavior for an auto-increment column. Why do you need the ID to be 6 and not 8? – AJ Richardson Jan 19 '15 at 02:49
  • Refer to this stackoverflow for answer: http://stackoverflow.com/questions/21824478/reset-identity-seed-after-deleting-records-in-sql-server – Keith Neuse Jan 19 '15 at 02:56

4 Answers4

0

You can't fix it. SQL server doesn't check the table for id's "holes", since it may decrease the performance.

If you need a "serial number" field, you have to implement this logic.

This logic is not needed often, in fact. Make sure you really need serial numbers, since their implementation is not trivial.

Mark Shevchenko
  • 7,937
  • 1
  • 25
  • 29
0

Think about these scenarios:

There are 4 connections inserting into the Table at the same time. 3 Connections start a Transaction for their Insert and then Rollback the Transaction. 1 Connection Commits their Insert.

There are 8 records in the table with ID's 1 - 8. I delete the even ID's 2, 4, 6, 8.

Don't worry about the Holes as you can't prevent them...

Mike Burdick
  • 838
  • 6
  • 5
0

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.

MANOJ GOPI
  • 1,279
  • 10
  • 31
  • That can have problems when 2 connections try to insert at same time. You would need to block other Inserts while you do this... – Mike Burdick Jan 19 '15 at 03:30
  • SQL Server internally handles it. 'I' in ACID property of database transaction defines it. – MANOJ GOPI Jan 19 '15 at 03:51
  • 1
    My point was that if 2 connections do this at the same time they may both get the same Max(IDName) value and then one connection will fail to insert. – Mike Burdick Jan 19 '15 at 04:00
0

I would suggest that you don't change anything in database, since the serial numbers can be generated at run-time in your C# program to show in dataGridView or any Controls.

Bhaskar
  • 1,028
  • 12
  • 16