0

I'm using an SQL Server 2012 from Microsoft. There I have a table with an id as primary key and autoincrement. Now I saw that the id makes big jumps, e.g.:

...

4001

4002

4003

14003

14004

24004

24005

...

Do you have an idea why the autoincrement makes so big jumps?

Bernard Vander Beken
  • 4,848
  • 5
  • 54
  • 76
  • someone might have Reseed the table again, or may be a large insert transaction rollback. – PSK Feb 07 '19 at 10:37
  • `BEGIN TRANSACTION; INSERT INTO T 10,000 ROWS; ROLLBACK`, `DELETE FROM T WHERE id BETWEEN 4004 and 14002`, etc – Salman A Feb 07 '19 at 10:37
  • 2
    That is a known feature of SQL Server. `IDENTITY` does **not** guarantee sequential values, and this is well documented: [Remarks](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-2017#remarks). If it's important that you store sequential values, `IDENTITY` isn't the solution. (Most likely your skip of 10,000 was due to an unscheduled shutdown/loss of power, and the cached ID's weren't released). – Thom A Feb 07 '19 at 10:37

1 Answers1

0

This issue can be related to IDENTITY CACHING.

The gaps is this case caused by restart of SQL Server instance, by failover, for instance. This is not a bug, but rather a performance feature.

IN SQL Server 2017+ it can be avoided by:

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF

However, it will affect performance of IDENTITY generation during inserts

In SQL Server 2012 seems there is no workaround

In previous versions trace flag 272 to be enabled

DBCC TRACEON (272,-1)
Alexander Volok
  • 5,630
  • 3
  • 17
  • 33
  • 1
    There's nothing wrong with how `IDENTITY()` behaves so there's no need for workarounds. Sequential values are provided by `SEQUENCE` objects. `IDENTITY`'s job is simply to provide unique incrementing numbers – Panagiotis Kanavos Feb 07 '19 at 10:43
  • @PanagiotisKanavos, why then Microsoft offered such setting? :) Perhaps some clients need it, right? – Alexander Volok Feb 07 '19 at 10:44
  • 1
    Earlier versions offer trace flag 272 for this; 2017 just makes it available without the need for fussing with trace flags. It has a definite performance impact, but some folks can afford not to care. – Jeroen Mostert Feb 07 '19 at 10:45
  • @JeroenMostert, thank you, will adjust my answer – Alexander Volok Feb 07 '19 at 10:46
  • That was already there as a trace flag. It's the same reason the values stopped being sequential 7 years ago. It's not just restarts. When there were only 2-4 CPUs, synchronization to ensure all of them produce sequential values wasn't *that* expensive. Nowadays we have 144 cores making synchronization prohibitevly expensive. Databases deal with a *lot* more data too. That's why identity caching was introduced with the option to disable it in the *unusual* case it caused problems – Panagiotis Kanavos Feb 07 '19 at 10:52
  • @PanagiotisKanavos, SEQUENCE also has the same effect. It also has caching and there are going to be GAPs in case of accidental restart of the instance unless NO CACHE specified, but the effect is the same as with that setting I mentioned. – Alexander Volok Feb 07 '19 at 10:52
  • @AlexanderVolok that's controlled by the `CREATE` statement of each individual sequence. It's not the same, nor does it have such wide-ranging implications. That's why *disabling* identity caching requires extraordinary steps by the DBA while SEQUENCE caching is up to the developer – Panagiotis Kanavos Feb 07 '19 at 10:54
  • @PanagiotisKanavos, of course :) – Alexander Volok Feb 07 '19 at 10:55