identity and rollback

This is more of an academic question and it related to SQL Server 2000.
I have a table that has an identity column. If i try to insert a row and
decide to roll back the transaction, the next insert will have a gap in the
numbering sequence of the identity column.
Is there a way to avoid the gap in the numbering?
Thanks.
ShahriarUnfortunately there is no way to avoid these gaps using an identity column.
You would have to implement your own solution according to your requirements
.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Shahriar" wrote:

> This is more of an academic question and it related to SQL Server 2000.
> I have a table that has an identity column. If i try to insert a row and
> decide to roll back the transaction, the next insert will have a gap in th
e
> numbering sequence of the identity column.
> Is there a way to avoid the gap in the numbering?
> Thanks.
> Shahriar|||> I have a table that has an identity column. If i try to insert a row and
> decide to roll back the transaction, the next insert will have a gap in
> the
> numbering sequence of the identity column.
> Is there a way to avoid the gap in the numbering?
Yes, don't use IDENTITY.
The advantage of IDENTITY is that it does not lock the entire table to
insert a row. You get "assigned" a value when you start your insert, and
since others may start an insert after that (but before you commit or
rollback), SQL Server has no choice but to resign your number as "taken"...
Why do you care about gaps?
The IDENTITY value itself has no real meaning - after all, the system
generated it for you. So, if you have a rollback, what does it mean, and to
whom, that there is no row where the surrogate identifier is = 4?
http://www.aspfaq.com/2523