Primary Key Identity Value Increments On Unique Key Constraint Violation

Question

I have a SqlServer 2008 table which has a Primary Key (IsIdentity=Yes) and three other fields that make up a Unique Key constraint.

In addition I have a store procedure that inserts a record into the table and I call the sproc via C# using a SqlConnection object.

The C# sproc call works fine, however I have noticed interesting results when the C# sproc call violates the Unique Key constraint....

When the sproc call violates the Unique Key constraint, a SqlException is thrown - which is no surprise and cool. However, I notice that the next record that is successfully added to the table has a PK value that is not exactly one more than the previous record -

For example: Say the table has five records where the PK values are 1,2,3,4, and 5. The sproc attempts to insert a sixth record, but the Unique Key constraint is violated and, so, the sixth record is not inserted. Then the sproc attempts to insert another record and this time it is successful. - This new record is given a PK value of 7 instead of 6.

Is this normal behavior? If so, can you give me a reason why this is so? (If a record fails to insert, why is the PK index incremented?)

If this is not normal behavior, can you give me any hints as to why I am seeing these symptoms?

Answer 1

Yes, this is normal.

Imagine transactions going on here and this being a potential order of operations ran on SQL Server.

1. IDs 1, 2, 3, 4, 5 are used.
2. Client A begins transaction.
3. Client A performs insert but does not commit (ID 6).
4. Client B begins transaction.
5. Client B performs insert but does not commit. (ID 7).
6. Client A rolls back.
7. Client B commits.

Because of the possibility for (not necessarily existence of) this behavior, you see that ID 6 gets skipped when that insert fails.