Identity values jumping by 1000 - IDENTITY_CACHE

Sometimes you will see a large jump of 1000 values in an identity column of a table. This can happen when SQL Server caches 1000 IDs for the table. If the SQL Service is restarted, these cached IDs can be lost, causing SQL Server to begin the next increment at the ID post the previous cache.

From SQL 2017, this behaviour can be turned off by setting IDENTITY_CACHE = OFF

From the SQL Server documentation:

SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.

Resources:

Pinal Dave's explanation and examples: https://blog.sqlauthority.com/2018/01/24/sql-server-identity-jumping-1000-identity_cache/

StackOverflow post about the issue, and workarounds if ID gaps are not acceptable: https://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database