0

Why does SQL Server sometimes not provide sequence no in identity columns? For example we were inserting records into the table; until 300 it was ok, but for the next record the id which was assign it 318 - an 18 numbers gap in identity column.

Can anyone tell me why? And I have seen this for other tables as well one number or two number gap between the before and after.

No records deleted

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mohammad
  • 71
  • 1
  • 10
  • 3
    Possible duplicate of [SQL Identity (autonumber) is Incremented Even with a Transaction Rollback](https://stackoverflow.com/questions/282451/sql-identity-autonumber-is-incremented-even-with-a-transaction-rollback) – melpomene Dec 09 '17 at 10:41
  • 1
    [Stop making assumptions about `IDENTITY`](https://sqlblog.org/2009/10/12/bad-habits-to-kick-making-assumptions-about-identity), stop worrying about gaps - they are really **NOT** a problem! – marc_s Dec 09 '17 at 10:43
  • Regarding why, SQL Server caches identity values and allows gaps in some cases to improve performance and concurrency. Think about IDENTITY values as incremental but not consecutive. – Dan Guzman Dec 09 '17 at 12:57

0 Answers0