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

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

Identity 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:


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:



See All Articles by Columnist Gregory A. Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles