Scenario
One of the common conversations I’ve come across in a typical work day is as below
Emp X: I just found that the IDENTITY column values for couple of tables have jumped to a new range
Emp Y: Can you check if there was any server restart?
Emp X: Oh yes! I see it now… There was a planned server restart at night.
The reason for this behavior is explicit.
Server restart causes identity based columns to skip values. This is due to the fact that identity columns pre-allocates (caches) next set of values and on server restart the cache gets flushed. Once server comes back it resumes at the next value from the maximum value in the cache which creates gaps in identity sequence. This behavior started from 2012 version onwards.
This blog discusses a new enhancement available in SQL 2017 CTP which can be used to solve the above issue
Solution
This behavior was explained to be by design and proposed solution was to use trace flag 272 which will allow the functionality to continue as per earlier version. The MS connect link which discusses this is given below
In SQL 2017, we have a good news. Microsoft has included this as a new option in SCOPED CONFIGURATION options for the database. The option is called IDENTITY_CACHE and is included in the documentation below
This has two advantages over the previous trace flag based method
1. We can set it from management studio itself rather than setting it as a startup option from SQL Configuration Manager.
2. The most important advantage is that we can scope it to database level rather than setting it at the instance level. This makes it possible for us to have other databases co-existing in the same server instance with new behavior enabled i.e. with gaps.
The complete syntax for the feature is as below
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;
Once you set it at a database level, the server restart doesn’t cause any gaps in the IDENTITY objects in the database, which is in line with the behavior before SQL 2012 version.
This small but very useful enhancement will certainly help the developers to set IDENTITY value behavior in a database as per their requirement.
