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
The scoped configuration setting for IDENTITY_CACHE can be enabled by using a statement as below
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;
Once above statement is executed the effect would be same as setting the trace flag 272 but at the database level.
This provides an added advantage to us due to the fact that we shall set it at the database level only for the databases where handling gaps in Sequence/Identity columns is a business problem and has to be avoided.
Wherever we need an optimal insert performance and cares little on the gaps in the values, we shall still keep the setting to the default which is ON where it maintains the cache for the values.
Conclusion
As explained above, the introduction of the new option in scoped configuration for controlling the identity cache behavior at the database level is very helpful in avoiding the identity value gaps issue in SQL 2017
No comments:
Post a Comment