SQL server skips identity value after error

Strange behavior of sql server which skips identity value after generating error. We created a table with identity key and one unique key column, added some records in it while inserting duplicate values in the table as expected sql server throws error saying violation of unique key.  After removing duplicate value when we are trying to insert new value in the table, it got inserted with mismatch in identity key.

As expected, identity values should be in sequence like 1, 2, 3 and so on. Here we got unexpected result as the 3rd value inserted with identity value “4”.

Lets explain this with help of below example, created one table with identity key and unique column.

create table identitytest

( idkey int identity(1,1),

Score int unique )

 

In Second step, we adding values in the table using below insert into statement.

insert into identitytest

values(50),

values(60)

 

In thrid step, we are inserting duplicate value to generate error as disscussed in above.

Insert into identitytest Values(50)

 

Msg 2627, Level 14, State 1, Line 2

Violation of UNIQUE KEY constraint ‘UQ__identity__E028AC056FE99F9F’. Cannot insert duplicate key in object ‘dbo.identitytest’. The duplicate key value is (50).

The statement has been terminated.

Now after this error, if we try to insert another value in the table as done below.

 

insert into identitytest values (70)

It will skip one value of identity column and generate the next value starting with 4 as shown below.

 

select * from identitytest

 

idkey       Score

———– ———–

1           50

2           60

4           70

 

(3 row(s) affected)

 

Strange behavior of sql server, ideally it should generate value in the sequence. Is this sql sever Bug ?

 

Leave a Reply

  

  

  


6 − = 1