SQL SERVER 2017 – IDENTITY_CACHE

28 Mar 2021 133 views 0 minutes to read Contributors

Introduction

The IDENTITY_CACHE option, is a new database scoped configuration option in SQL Server 2017 and Azure SQL Database. This option is set to ON by default. The concept of identity caching in SQL Server is that the Database Engine stores in cache a series of values for identity columns and use them whenever needed in order to be faster. Due to server restart or database failure some assigned values may be lost.  If your application required to have consecutive values, then having identity column was the right choice prior to SQL Server 2017.

You need to write your own mechanism to generate key values without any gap. Using sequence with NOCACHE option we can limit the gaps to transactions that are never committed.

Now in SQL Server 2017 we can also able to disable the identity cache at the database level using Alter Database scoped configuration.

This is similar to the Trace Flag 272 setting with the difference that the IDENTITY_CACHE option is within the database scope and not within the server scope.

 

Demo

We will create a table and insert some data into it.

 

 

Now, insert some rows in SQLT_IdentityCache.

 

Insert into SQLT_IdentityCache (RecordDate)

 Select GetDate()

WaitFor Delay '00:01'

Go 5

 

Now, let’s check the data we inserted

 

 

Here, we can see that 5 rows are inserted into the table and have IdentiyId column values in the sequence manner without any gaps.

Now, we will insert more rows within a transaction but we will not commit it.

 

 

Now, we will stop the SQL Services using SHUTDOWN WITH NOWAIT command.We can also stop SQL Services via SQL Server Configuration Manager.

 

Now we will start SQL Services.

 

 

Now we will re-insert data in SQLT_IdentityCache table

 

 

Now we will again check table SQLT_IdentityCache table

 

 

You will also be noticing the gap in identity sequence. To overcome this scenario, SQL Server 2017 has the facility to disable Identity Cache which prevents the gap in an identity column.

Now, we will re-run the same scenario with disabling the identity cache.

 

We will create new table on which we will test switching off Identity_Cache scenario.

 

 

 

 

Now we will disable the Identity cache,

 

 

 

We can confirm this change in system catalog view sys.database_scoped_configurations

 

 

Now we will insert data into new table SQLT_IdentityCacheOff,

Now again we will insert data and won’t commit in SQLT_IdentityCacheOff on which we have switched off Identity_Cache.

Now we will stop sql services without committing previous transaction using command SHUTDOWN WITH NOWAIT and will start the services again.

 

 

Now we will again insert the data from a new query window, and verify the gap in identity sequence.

 

 

You have noticed there is no gap in identity sequence even after an unexpected restart of a SQL server instance.

So after disabling the identity cache we will overcome with the problem of gaps in the values of identity column when a SQL Server instance restarts unexpectedly.

 

In this article