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

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,
DerekThis 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

Identity Column

Hi all,
I am using a identity column in my table. When any rows are deleted in the identity column. The next value is not assigned to the previously deleted number. ie In one column iam generating sequence of number from 1 to 10. for example, the current row is 5. Now iam deleting the row 5. Next number should come only 5. Instead of that, the next value generated is 6. Can u suggest me any idea on this. Of couse, it is the default property how to over come this. Pls help me.

HenryHi there
AFAIK identities (auto assigned) are unique - that means used just once.

I had the same usage for a identity-row, i've simply used non auto-assignment and a procedure wich searches for the lowest free key and assigns it...

greetz
c0r0n3r|||This is the nature of the IDENTITY property. The following is an excerpt from BOL:

"If an identity column exists for a table with frequent deletions, gaps can occur between identity values. If this is a concern, do not use the IDENTITY property. However, to ensure that no gaps have been created or to fill an existing gap, evaluate the existing identity values before explicitly entering one with SET IDENTITY_INSERT ON."

So basically if you continue to use the IDENTITY property and wish to attempt to fill the gaps you will have to use the SET IDENTITY_INSERT ON statement.

coroner already mentioned an alternative method if this is a problem. Others just ignore the gaps. Or you can use the method mentioned above.|||Thank u rnealejr & coroner. It was so useful to me.
Thank u so much.|||I'm wondering where SQL Server saves this information about what the "next" generated identity number should be?

i.e How does it know it's been used and deleted.

thanks
js

identifying the invalid procedure

Is there any option to identify the invalid procedure objects. Say for example if procedure P1 uses a table T1 and the table T1 is now deleted then P1 procedure will not be executed. I need to identify these procedures.
Any guidance?
sql2k suppors 'defered name resolution' thus it's perfectly legal to have a stored procedure that references a non-existent object. However, it's important to note here is that if the referenced object exists prior to the stored procedure creation, then an entry is made in sysdepends for the sproc and the object. Hence, you can use sp_depends to find dependencies.
e.g.
--a valid proc
create proc bogus
as
select *
from nonexistencetable
go