-1

What is the exact reason for identity increment jumping after restarting SQL Server?

user3077222
  • 99
  • 11
  • 3
    See [this question](http://stackoverflow.com/questions/14162648/sql-server-2012-column-identity-increment-jumping-from-6-to-1000-on-7th-entry?rq=1) and many more like it. If you want sequential numbers without gaps, `IDENTITY` is the wrong tool. But more likely, the desire for sequential numbers without gaps is the wrong demand and identity is a fine tool. – Damien_The_Unbeliever Mar 24 '15 at 07:57
  • 1
    There are things that use Identity values and they're not put back in case of rollbacks/non-commits etc because identity isn't a guaranteed unbroken sequence - and shouldn't be used as such. – Allan S. Hansen Mar 24 '15 at 07:57
  • not desiring or demanding sequential numbers ..just wanted to know the reason why does it happen – user3077222 Mar 24 '15 at 08:52

1 Answers1

0

If I understood correctly then you are probably facing this issue Failover or Restart Results in Reseed of Identity. The reason is, when you allocate the IDENTITY values then a cache size of 1000 is allocated, so when you are restart the unused values are lost.

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331