Introduction

This article is about how to avoid the gaps in the identity column value by disabling the identity cache. This change is made in SQL Server 2017.

Explanation 

One of the new changes in SQL Server 2017 is the ability to disable the cache to avoid the gaps in the value of identity column when the server restarts unexpectedly or fails over to a secondary server.

This option is similar to the existing Trace Flag 272, except that it can be set at the database level rather than only at the server level.

IDENTITY_CACHE = { ON | OFF }

The default setting is ON.

We can enable or disable the identity cache at the database level using statement ALTER DATABASE SCOPED CONFIGURATION.

Let’s jump to the demo to have the better picture of this.

Demo

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

CREATE TABLE dbo.Test_Identity
 
(
 IdentityID INT IDENTITY(1,1),
 TodayDate DATETIME
 )
 
GO

Now, insert some rows to it 

INSERT INTO dbo.Test_Identity (TodayDate)
 
SELECT GETDATE()
 
GO 5

Then, check the data inserted into the Test_Identity table 

SELECT * FROM Test_Identity



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.

BEGIN TRAN
 
INSERT INTO dbo.Test_Identity (TodayDate)
 
SELECT GETDATE()
 
GO 5

Now, we will stop the SQL Services.

You can stop the SQL Services in two ways 

  1. From SQL Server Configuration Manager
  2. Using SHUTDOWN WITH NOWAIT command.

Open, a new query window and execute the following command 

SHUTDOWN WITH NOWAIT

Once you executed the above command you will see the below message in the output window -

Once the execution completed, we will go to the SQL configuration manager and restart the SQL Services.

Open another new SQL query window and insert few rows 

INSERT INTO dbo.Test_Identity (TodayDate)
 
SELECT GETDATE()
 
GO 5

Now, we will check the data in the table 

SELECT * FROM Test_Identity

Here, we can see the gaps in the IdentityID column and it started inserting from Id value 1002.

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.

To reset the demo, drop the table.

DROP TABLE Test_Identity
 
GO

Recreate the table Test_Identity

CREATE TABLE dbo.Test_Identity
 
(
 
                IdentityID INT IDENTITY(1,1),
 
                TodayDate DATETIME
 
)
 
GO

Now, disable the Identity cache

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
 
GO

You can check the value for Identity_Cache from the DMV sys.database_scoped_configuration.

Now, insert data to table Table_Identity

INSERT INTO dbo.Test_Identity (TodayDate)
 
SELECT GETDATE()
 
GO 5

Again, we will insert some rows within a transaction but will not commit it.

BEGIN TRAN
 
INSERT INTO dbo.Test_Identity (TodayDate)
 
SELECT GETDATE()
 
GO 5

Stop the SQL Services.

SHUTDOWN WITH NOWAIT

Restart the SQL Service from configuration manager and again insert the data from a new query window –

INSERT INTO dbo.Test_Identity (TodayDate)
 
SELECT GETDATE()
 
GO 5

Now, check the data in the table –

SELECT * FROM Test_Identity

So, this time we have no gaps in the identity columns even after an unexpected restart of a SQL server instance.

Conclusion

With 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.

See Also

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql