Another reason for gaps in IDENTITY

Another reason for gaps in IDENTITY

The 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.

Microsoft doesn’t mention the issue in their documentation, neither on the INDEX_OPTION page nor on the IDENTITY page.

Solutions to consider

  1. Reseeding the Identity after each instert. This is neither elegant nor too easy, because the desired value need to be set explicite for the DBCC RESEED command.
  2. Rewriting Insert to Merge. Keep in mind that Merge has other problems you should consider.
  3. Make the application ignore the duplicate key error – again, not very elegant.
  4. 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.


Using 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 IDENTITY.

The header image used in this entry is downloaded from

Related Posts

Leave a reply