SQL SERVER – Identity Jumping 1000 – IDENTITY_CACHE

SQL Server 2017 has a wonderful feature which fixes a very old issue which DBA and developers have been struggling for a while. It would be impossible to find DBA or Developer who has not faced an issue when their identity column value has jumped or incremented by 1000. It is a very common issue and today we will see the solution of this issue with the help of IDENTITY_CACHE introduced in the latest version of SQL Server.

First, let us understand the issue which we face when we keep IDENTITY_CACHE = ON for SQL Server. Please note that as this is a default value is ON, so if you do not change anything you will see the following behavior.

Case 1: IDENTITY_CACHE = ON — default

First, make sure that value of identity cache is set to on by running following command.

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = ON
GO

Create a sample table where there is one column which is identity.

IF OBJECT_ID('dbo.Identity_Cache_Demo', 'U') IS NOT NULL
DROP TABLE dbo.Identity_Cache_Demo;
GO
CREATE TABLE Identity_Cache_Demo
(ID INT IDENTITY(1,1) PRIMARY KEY,
Colors varchar(50));
GO

Now let us insert few rows into the table and right after that let us insert few rows into the table.

INSERT INTO Identity_Cache_Demo (Colors) VALUES ('Red');
INSERT INTO Identity_Cache_Demo (Colors) VALUES ('White');
GO

Next, let us check the values in the table by selecting the data.

SELECT *
FROM Identity_Cache_Demo
GO

You may notice that the value of the ID column is 1 and 2.

Now we will run the following command inside a transaction. Please note that we will just begin the transaction but we will not complete the transaction.

BEGIN TRANSACTION
INSERT INTO Identity_Cache_Demo (Colors) VALUES ('Brown');
INSERT INTO Identity_Cache_Demo (Colors) VALUES ('Pink');

Now we reached to a very important stage. We need to create a crash for SQL Server. To recreate unexpected shutdown, go to a New Query Window and run following command.

-- Run only in new query window
SHUTDOWN WITH NOWAIT -- New Window
GO

Now connect to SQL Server once again by turning on the services.

If you run the select command once again, you will not see the data which we had initiated in the transaction because the transaction was never complete and the data was never committed. This is correct behavior.

SELECT *
FROM Identity_Cache_Demo
GO

Now let us insert a couple of more rows into the table.

INSERT INTO Identity_Cache_Demo (Colors) VALUES ('Blue');
INSERT INTO Identity_Cache_Demo (Colors) VALUES ('Orange');
GO

Once again we will run the select command see what is the value of the ID column.

SELECT *
FROM Identity_Cache_Demo
GO

You will notice that there is a gap of 1000 value in the Identity. This is because SQL Server had generated and cached the next 1000 identity values for the table. SQL Server does this for the improve the performance of the INSERT statements which runs on the table. However, due to the unexpected crash scenario, it also loses the pre-generated identity value and that behavior is the responsible for the 1000 value jump in the identity.

If you want to overcome this issue, you have to follow the CASE 2 where we keep the identity cache value to OFF.

Case 2: IDENTITY_CACHE = OFF

In this scenario, we will run the following script to turn off the identity cache.

-- Case 2 IDENTITY_CACHE = OFF
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
GO

Once we do this, we will run exactly the same scenario we ran in case 1 once again. As I have explained all the steps in detail in Case 1, I will directly jump the scenario where we insert values after the unexpected shutdown.

Please repeat all the steps which you have followed in step 1 and after once again run the SELECT statement. Over here you will notice that this time, it does not jump or miss any identity value.

SELECT *
FROM Identity_Cache_Demo
GO[/sql

However, when you keep your identity cache value to OFF, your SQL Server takes a very little performance hit, which you can safely ignore if you do not see any CPU or Memory Issues.

This feature can be very important when you are dealing with replication with heterogeneous databases. If you are doing replication between multiple database platforms, I suggest that you take a look at SharePlex. With the help of SharePlex, you can achieve high availability, scalability and data integration through real-time database replication across on-premises, cloud or hybrid databases.

Reference: Pinal Dave (https://blog.sqlauthority.com)