SQL 2017 Tips: Avoiding Gaps in IDENTITY, SEQUENCE Based Column Values

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

https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity

For setting this we had to go to SQLServer configuration manager and set trace flag 272 in the startup parameters as discussed here

http://sqlblog.com/blogs/kalen_delaney/archive/2014/06/17/lost-identity.aspx

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

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql

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.