- Docs
- /
28 Mar 2021 417 views 0 minutes to read Contributors
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.
We will create a table and insert some data into it.
T-SQLTransact-SQL
1 If Db_id('SqlTreeoDemoDB') is null<br /><br />Create Database SqlTreeoDemoDB<br /><br />CREATE TABLE dbo.SQLT_IdentityCache<br /><br />(<br /><br /> IdentityID INT IDENTITY(1,1),<br /><br /> RecordDate DATETIME<br /><br /> )<br /><br />GO
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.
T-SQLTransact-SQL
1 Begin tran<br /><br />Insert into SQLT_IdentityCache (RecordDate)<br /><br />Select GetDate()<br /><br />WaitFor Delay '00:00:10'<br /><br /> Go 5
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
T-SQLTransact-SQL
1 Insert into SQLT_IdentityCache (RecordDate)<br /><br /> Select GetDate()<br /><br />WaitFor Delay '00:00:10'<br /><br /> Go 5
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.
T-SQLTransact-SQL
1 CREATE TABLE dbo.SQLT_IdentityCacheOff<br /><br /> (<br /><br /> IdentityID INT IDENTITY(1,1),<br /><br /> RecordDate DATETIME<br /><br /> )<br /><br /> GO
Now we will disable the Identity cache,
T-SQLTransact-SQL
1 ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF<br /><br /> GO
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
Please choose a monitoring environment to connect to:
Enter your email address to recover your password.
Download link successfully sent. Check your email for details.
Reset password link successfully sent to . Check your email for details.
An email with a link for creating your password is sent to
Share link successfully sent to .
Your reply is send.
Download link successfully sent to . Check your email for details.
Download link successfully sent to . Check your email for details.
Download link successfully sent to . Check your email for details.
Download link successfully sent to . Check your email for details.
We received your request. We will contact you shortly