Increment Primary key

[dr223]

Hallo,

I am working with Sql server 2005 and Vb.net.
I have a table with 3 fields - pay_id, pay_name and account_num.

pay_id is set as Primary Key and is automatically incremented when a record is added. Now, when you delete a record(s), and add another record the pay_id remembers the last number (even if deleted) and increments to create the new pay_id. For example, I had the records below

pay_id pay_name account_num

2 John 7836434
3 Ali 3473443
4 James 8243245

If the last record is deleted (pay_id = 4), the next new record will have a pay_id of 5 as shown below;

pay_id pay_name account_num
2 John 7836434
3 Ali 3473443
5 Julius 2752457

Notice, pay_id is 5 rather than 4. Therefore I have two problems;

1) I want to amend this so that when a record is deleted then it reads the most present last record and increment the pay_id..
2) My table (tblpay) pay_id's are so random due to deletion of records and creating new records. What will be the easier way of having pay_id's start from 1 onwards in an incremental fashion...

Thanks

[Campion]

They aren't random at all. An AUTO_INCREMENT (or IDENTITY, as MS calls it) column does just what it says it does: it increments a counter to provide for a unique row, even if other data is the same. It will NEVER insert into a previously used row ID, nor should you force it to.

[jmcilhinney]

I want to amend this so that when a record is deleted then it reads the most present last record and increment the pay_id

Why? What purpose would this serve?

[Shaggy Hiker]

It's neat?

Technically, you can do this by altering the settings for the primary key field, but don't do it.

[Pradeep1210]

2 John 7836434
3 Ali 3473443
4 James 8243245

What if you delete records from in between (say record #3). Won't you still have records out of order?

[NickThissen]

What does it matter if the ID's are neatly in order without skipping numbers? The ID is usually just a number to identify that particular record with. The user, nor you really, should never have to even see it. Your application should function the same way whether the ID's are neatly in order or not.

If you still REALLY want to do this, perhaps an option is to create another column, DELETED, which tells you if that particular record is deleted or not. This way, you never actually delete a record, but you merely set the DELETED flag (set it to 1, for example, when 'deleted', and 0 otherwise). Then, in all your queries, you simply add a where clause "where DELETED = 0". The behavior will be the same as if you had really deleted the rows, and your ID's should remain neatly in order, and you have to added advantage of the customer never losing records, as they are still in the table. You could then create a simple 'restore' functionality that resets the DELETED flag of any deleted records. Obviously, this may not be an option if you are working with huge tables where you simply need to remove records to be able to cope with the size.