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
  • 704,970
  • 168
  • 1,303
  • 1,425
Mohammad
  • 61
  • 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`](http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/12/bad-habits-to-kick-expecting-identity-to-mean-something.aspx), 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