0

While working on the SQL Server database, I found that identity column value suddenly jumps to 101 in SQL server 2012 Enterprise edition.

There were total 10 records into database with the IDs (1 to 10). There were no delete operation was performed and while inserting the record number 11 it tool 101.

And I found out the cause after browsing some online helps:

Here, Here and Here.

Is there any other cause behind happening this?

Community
  • 1
  • 1
Dhaval Panchal
  • 612
  • 4
  • 12
  • 32
  • 3
    A search for `SQL Server 2012 identity jump` should find you plenty of other questions/answers. But as always, I'd advise this: If the actual numeric values of identity columns matter to you, you're doing something wrong. They should be treated as opaque blobs that just *happen* to be able to fit inside numeric columns. – Damien_The_Unbeliever Dec 04 '13 at 08:09
  • Is your employer OK with publishing all the info about your project in a screen shot? I can see the company the project is for, the database schema, IDs... – Remus Rusanu Dec 04 '13 at 08:17
  • Using sequences in SQL Server 2012? – bjnr Dec 04 '13 at 09:17
  • Thank you for providing feedback. I would have to use sequence to solve this problem. – Dhaval Panchal Dec 04 '13 at 10:17

1 Answers1

6

Is possible by design. Identities are not guaranteed to be contiguous nor monotonous. Nobody can help, because there isn't any help needed. Applications should never rely on identity not having gaps. Having identity value rows 1,2, 100, 1000 is always OK.

How is this possible? Well, to start with, consider the trivial case of DELETE. Obviously deleting rows would leave gaps behind.

A more subtle problem is when gaps in identities appear even though there are no deletes. This happens because there are deletes, you are just not aware of them. They are caused by uncommitted transactions rolling back. Rollbacks cannot be prevented, since banning rollbacks is akine to saying 'This will always succeed!', ie. ignoring reality (failures). A client disconnecting in a middle of an insert is a rollback and you can never prevent that.

Always expect gaps. Code expecting gaps. Never rely on identities being contiguous.

I would have to use sequence to solve this problem

Sequences have exactly the same problem. This is not some oversight on design, this is a fundamental issue: there is no performant way to generate contiguous, gap free IDs. The only solution is to serialize all inputs (eg. table X lock) and nobody wants to do that.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • In other words, if you rely on the ID being a nice value, don't use identity - just manage the IDs yourself, your way. – Luaan Dec 04 '13 at 08:30
  • 2
    @Luaan: and how exactly would you manage IDs to be 'nice', in presence of DELETEs and rollbacks? This is a [fools errand](http://en.wikipedia.org/wiki/Snipe_hunt). – Remus Rusanu Dec 04 '13 at 08:31
  • Oh, yeah, totally. I'm more into the scenario that it seems that OP has some kind of Question Type, which is probably referenced somewhere in code. If it's referenced in code (and not in a dynamic, modular way), it would be better to simply have a static ID, rather than allow creating new ones. So, at the very least, a different QuestionType table, which actually says what the question type *is* (and should do), rather than just having one per MarketplaceID. This table would be non-user-editable, and bound to the code. – Luaan Dec 04 '13 at 08:36