Question: When I delete a record in a table using SQL Server 2008 and its Management Studio, and then insert a new record, sequence of primary key column is not in order. 

Suppose I delete Record5 with `record_id = 5`. Table is left with 4 records i.e., `Record1,Record2,Record3,Record4`. 

Now when I insert a new record .. its `ID` (primary key) is automatically set to 6. I need SQL Server to set it as 5. 

Because it looks weird when I display this table in a gridView in Asp.Net (c#), my table's `record_id` column sequence is something like `1, 2, 3, 17, 18, 29` etc., 

It looks very bad. Please help. 

Thanks

Answer: 
You need to order the diplay of your data separately from the identity column in your database. The identity column is not for "display order", in fact, it does not have any *discernable* order - it will always have holes.

Consider a table with 4 rows. Now you add 5, then delete 5 rows. The next column you enter with have its ID as 10, even though you only have 5 rows in the table.

So, stop relying on that. Instead use [`Container.ItemIndex`](https://stackoverflow.com/questions/1589316/asp-net-listview-container-dataitemindex) and use that to display the appropriate count of the number of rows being shown.
After your Delete statement run this Queries.

```
declare @a int

set @a =(select max(identity_column_name) from table_name)
--print @a

dbcc checkident(table_name,reseed,@a)

```