Identity Column Increment Control

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Derek Shi - 22 Oct 2004 21:08 GMT

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

Reply to this Message

Vishal Parkar - 22 Oct 2004 22:20 GMT

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')

Signature

Vishal Parkar
vgparkar@yahoo.co.in | vgparkar@hotmail.com

Reply to this Message

Vishal Parkar - 22 Oct 2004 23:53 GMT

>> 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.

Signature

Vishal Parkar
vgparkar@yahoo.co.in | vgparkar@hotmail.com

Reply to this Message