Resolved Is it possible to retain auto increment even after deleting a tuple from a datagridview

[Omer Butt]

I want to ask if there is any possibility to retain the Auto Increment Primary Key Numbers after deleting a record. For Example there are 5 records save in a table if I delete a tuple ID 3 by clicking on a delete button from a datagridview it shows the ID sequence retained as 1,2,3,4 instead of 1,2,4,5.

Where it also should not disturb the database itself ..

If possible please explain how can we achieve this ?

 

[JuggaloBrotha]

Why would this matter?
I mean if you're using Sql Server you could write a trigger to re-assign all of the ID's sequentially for the table's records after delete, but why would that matter?

 

[jmcilhinney]

This is a silly request. If the ID value actually matters then you shouldn't be using an auto-incremented value in the first place. The whole point of such an ID is that it is generated without you having to care about it, so stop caring.

 

[Omer Butt]

What if it matter only for visual representation ?

 

[jmcilhinney]

What if it matter only for visual representation ?

It wouldn't. The ID is to uniquely identify the record and it can do that whether there are "holes" in the sequence or not. In fact, it will do it better if you leave the holes after deleting a record because then the subsequent values won't then identify a different record than they did before. If you're just trying to do this because you think it looks nicer then get over it. If you want to display records with a sequential record number with no holes then just assign a record number when you perform a query. That's what SSMS does, for instance. If you query a table then it will display record numbers specific to that query in the row headers and then the actual IDs in a column. Basically, what you're suggesting is a bad idea and you should not do it. You're not the first person to ask and everyone gets the same answer.

 

[Skydiver]

What if it matter only for visual representation ?

As my database teacher in college said, "IDs are for the use of the system. If you want a human readable record identifier, use a separate column." Then he started a discussion of vanity plates for cars and whether the plate number should be used as a database row ID or not.