How to Eliminate Gaps in Identity Values in SQL Server by Disabling the Identity Cache

Have you ever heard of the identity cache? If not, it is just a cache that stores the next 1000 available identity cached values for a table. The identity cache was introduced in SQL Server 2012 and is used to improve the performance of generating new identify values. The problem with this cache is, if SQL Server should abort or be shutdown unexpectedly, SQL Server loses track of the values in the identity cache. When this occurs, SQL Server gets another set of 1000 identity values, and you end up with gaps in your identity values.

With the introduction of SQL Server 2017, the SQL Server team introduced a new database configuration options named “IDENTITY_CACHE” that can be turned on and off. This new database configuration options allows you to disable the identify cache by database. By disabling the identity cache, you can avoid the large gap in identity column values when SQL Server should crash or be shut down unexpectedly. In prior releases you could have turned on trace flag 272 to disable the identify cache, but this trace flag disabled the identify cache at the server level and not the database level.

The IDENTITY_CACHE database option is on by default. If you want to set up your database to disable the identify caching functionality you will need to set the IDENTITY_CACHE options to OFF. By setting the IDENTITY_CACHE option to OFF, you avoid the numbering issues when SQL Server restarts.

To identify your current IDENTITY_CACHE setting for your database run the following command:

SELECT * FROM sys.database_scoped_configurations
WHERE NAME = 'IDENTITY_CACHE';

When I run this command in the context of one of my SQL Server 2017 databases, I get the following output:

Here you can see that the IDENTITY_CACHE value is set to 1, which means the identity cache is enabled.

If I want to disable the identity cache for my current database, all I need to do is run the following command:

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF;

If you are having problems with large gaps appearing in your identity column values, you consider turning off the identify cache.

If you need to turn the IDENTIFY_CACHE option back on for your database, you can run the following command:

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=ON;

###

See All Articles by Columnist Gregory A. Larsen