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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
Free Newsletters:



News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

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

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:

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



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