Identification of a primary key value with a unique violation of key constraints

Question

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

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

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

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

For example: let's say that the table contains five records, where PK values ​​are 1,2,3,4 and 5. Sproc tries to insert the sixth record, but the Unique Key restriction is violated, and therefore the sixth record is not inserted. Then sproc tries to insert another record, and this time it succeeded. - This new entry receives 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 the record is not inserted, why does the PK index increase?)

If this is not normal behavior, can you give me any clues as to why I see these symptoms?

Answer 1

Yes, it is normal.

, , , SQL Server.

• 1, 2, 3, 4, 5.
• A .
• A , (ID 6).
• B .
• B , . (ID 7).
• A .
• B .

- ( ) , 6 , .