sql - Table ID (PrimaryKey) does not increment in a proper sequence when a new record is inserted -

this question has answer here:

• why there gaps in identity column values? 7 answers

when delete record in table using sql server 2008 , management studio, , insert new record, sequence of primary key column not in order.

suppose delete record5 record_id = 5. table left 4 records i.e., record1,record2,record3,record4.

now when insert new record .. id (primary key) automatically set 6. need sql server set 5.

because looks weird when display table in gridview in asp.net (c#), table's record_id column sequence 1, 2, 3, 17, 18, 29 etc.,

it looks bad. please help.

thanks

this may sound unbelievable, if want incrementing record number, sql server has no support you. transaction rolled or server restart can leave holes in numbers.

for strictly increasing order, have roll own implementation. 1 common solution create table list of handed out numbers. if retrieve , increase number in atomic manner, thread-safe. example:

update  numberstable set     nr = nr + 1 output  deleted.nr   type = 'ordernumber' 

another option dynamically retrieve highest order number. appropriate locking hints, can done in thread-safe way:

insert  orderstable         (ordernr, col1, col2, col3) select  isnull((         select  max(ordernr) + 1            orderstable (tablock, holdlock)         ), 1) ,       'value1' ,       'value2' ,       'value3' 

if delete row, you'll have implement manually. records 1, 2 , 3 exist. delete record 2. number should new order get? if 2, remember means order 2 created after order 3, confuse lot of people.