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: 
This may sound unbelievable, but if you want an incrementing record number, SQL Server has no support for you. A transaction that is rolled back or a server restart can leave holes in the numbers.

For a strictly increasing order, you have to roll your own implementation. One common solution is to create a table with a list of most recently handed out numbers. If you retrieve and increase the number in an atomic manner, that is thread-safe. For example:

```
update  NumbersTable
set     Nr = Nr + 1
output  deleted.Nr
where   Type = 'OrderNumber'

```

Another option is to dynamically retrieve the highest order number. With the appropriate locking hints, that can be done in a thread-safe way:

```
insert  OrdersTable
        (OrderNr, col1, col2, col3)
select  isnull((
        select  max(OrderNr) + 1
        from    OrdersTable with (tablock, holdlock)
        ), 1)
,       'value1'
,       'value2'
,       'value3'

```

If you delete a row, you'll have to implement that manually. Say that records 1, 2 and 3 exist. You delete record 2. What number should the new order get? If you say 2, remember that means order 2 is created after order 3, which would confuse a lot of people.
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)

```