Identity Column Increment Control

Hi,
I have a table with identity column. I set the increment
to 1. But after I have deleted a couple of rows then
inserted a new row, the increment is not based on the
existing row number. For example, I had 100 rows already.
After I deleted two rows from the bottom., the last row I
have is 98. Then if I insert another row, it starts from
101 instead of 99. How can I solve this problem.
Thanks,
Derek
This is expected. The next identity value will not be in sequence, you will
see gaps in the identity values. The increment is not based on the existing
row number, however it will be the next of last generated identity value for
the table.
You can use "dbcc checkident" to reset the identity value of the table.
ex:
create table tt(i int not null identity, ii varchar(6000))
go
insert into tt (ii) values('x')
insert into tt (ii) values('y')
go
delete from tt where i = 2
go
DBCC CHECKIDENT (tt, RESEED, 1)
GO
insert into tt (ii) values('z')
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||>> The increment is not based on the existing
row number, <<
I mean to say, it is not based on the last value of the identity column.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com