clever lazy way
When creating indexes in SQL Server you can use option
IGNORE_DUP_KEY. It seems very handy, for example for dictionaries – you can just keep inserting, if the entry already exists then it just doesn’t insert. Seems so nice, no duplicate, no error… no problem?
There is one problem. When you try to insert a row which already exists it looks like nothing happens. Actually one thing does happen:
IDENTITY goes up by number or the not inserted rows .
If you planned a tiny dictionary and thought that a tinyint will be sufficient for the
IDENTITY field you may be painfully disappointed.
Solutions to consider
- Reseeding the Identity after each instert. This is neither elegant nor too easy, because the desired value need to be set explicite for the
- Rewriting Insert to Merge. Keep in mind that Merge has other problems you should consider.
- Make the application ignore the duplicate key error – again, not very elegant.
- Check for existance before inserting. Make sure it is transactional so that other sessions don’t interfere. This is the best way to go in most cases.
IGNORE_DUP_KEY is a good idea only when the table doesn’t have an Identity column but uses a natural primary key.
Further read: Other reasons for gaps in