SQL 2017 Tips: Tweaking IDENTITY_CACHE Setting to Avoid IDENTITY Column Gaps

Introduction

In my discussions with my fellow colleagues as well as on my online interactions with the developer community, I’ve come across a good amount of cases where people have raised a concern over the IDENTITY behavior in SQLServer.
As you probably know, IDENTITY based columns have a tendency to jump values whenever server restart occurs in the recent versions. Though this doesn’t cause any problems regarding the sequence of values being generated, there are some use cases where I’ve seen this causing an issue for the application especially when the column is being expected to maintain the continuity in values and avoid gaps. Though its certainly a debatable topic on whether

Root Cause

In reality, the cause of this issue is purely intentional and by design.

The IDENTITY values are cached for improving the performance of insert operations on tables with IDENTITY columns. So if we had to dispense with this IDENTITY behavior our only option was to utilize the trace flag 272. This had to be set either using DBCC commands or using -T startup option.

SCOPED CONFIGURATION 

SQL 2016 introduced SCOPED CONFIGURATIONs which allows configuration settings to be applied at a database instance level.
In SQL 2017 there was a new option added to it namely IDENTITY_CACHE which can be used for solving the above issue. Once set for a database, this setting ensures the identity values are not getting cached within the database. This will avoid the problem of IDENTITY column jumping values during the restart of a SQL Server.

Syntax