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: 
There is no guarantee that the IDENTITY values will not have gaps. In fact is very likely to encounter such gaps. You must design your application in a way that accommodates and expects such gaps in identity values. To display a row number in a grid the best option, by far, is to use a local client-side (ASP.Net or even browser side JS) counter. You can generate the counter server side using [`ROW_NUMBER()`](http://msdn.microsoft.com/en-us/library/ms186734.aspx) but is not the best options compared to client side.
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)

```