Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 20, 2019

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

By Greg Larsen

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM