SQL Server rollback issue

[Guest]

I'm experiencing an odd behavior in SQL Server 2000. I'm pretty sure this
qualifies as a bug. I have a transactioon that contains several different
record types, all of them using an identity element as their primary key. If
there's a problem with a transaction, a rollback is issued to remove all the
records. The rollback correctly removes all the records, but the identity
elements remain incremented as if the records were still in the database. For
example, let's assume the last successful transaction header had an identity
element value of 1000. I attempt another transaction that rolls back. I then
attempt a new transaction that succeeds, but it gets an element value of
1002, not 1001.

The program experiencing this behavior is written in C# under .Net version
1.1. I'm using the SQLClient object set and connecting via the current
version of ADO. The operating system and SQL Server are up to date with
service packs.

Is this a SQL Server bug or am I missing something?

 

[William \(Bill\) Vaughn]

Nope. This behavior is "by design". Once a number is generated, it's not
created again. It's not unusual for identity values to have gaps.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Miha Markic [MVP C#]]

Jup, Bill is right.
Why do you bother?