SQL SERVER – Identity Jumping 1000 – IDENTITY_CACHE

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

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