Identity column on table and Ignore Duplicate on Index

Hello,

I have a table with an Identity Column set up. I also have an index on the table that is set to Ignore Duplicate. Identity starts at 1 and is incremented by 1.

So first 5 rows inserted get identity

1

2

3

4

5

If I insert rows that get ignored because of the index with Ignore Duplicate, it is ignored correctly. But, the next row to get inserted will have an identity value of 7.

So, even though the insert was ignored because of the index with Ignore Duplicate, the Identity column was incremented behind the scenes.

Is there any way to avoid this?

Thanks,

John

DBCC CHECKIDENT with the reseed option.|||The value that is used to create the unique number is incremented each time an insert is done against the table. However, it that insert is rolled back (either because of user rollback or server rollback), the number will not be reset, as this would cause problems with multiple transactions getting unique numbers at the same time.

How important is it that there are no gaps in the numbers, i.e. is there a reason why you want to avoid gaps?

Thanks,|||The identity field is guaranteed to be UNIQUE, NOT SEQUENTIAL. This is a common misconception.

DO NOT do anything that depends on not having gaps. You will be sorry in the long run.