No auto increment for the PK on duplicate insert with IGNORE_DUP_KEY = ON

entity-framework sqlbulkcopy sql-server

Question

I set IGNORE_DUP_KEY = ON so there is no warning when we try to insert lots of data. However, the primary key does increase for each duplicate row, so one could go from Id 100 to 120 when you have 20 duplicate rows after each other. Is there any way to let it continue at 101 without manually setting the value? I'm using SqlBulkCopy and Entity Framework and I'm not interested in a SQL query to workaround this problem (e.g. using NOT EXISTS). Of course, we could check first if a record already exists but we prefer not to for performance reasons.

Accepted Answer

If you require continuous identity values then IDENTITY is not for you. There is absolutely no guarantee that identity values cannot go missing. You scenario is not the only case where this happens.

Either manage IDs yourself (using a sequence, for example; not easy to integrate into a bulk load) or give up on the requirement to have uninterrupted IDs.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow