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

We will create a table and insert some data to 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 to 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 unexpected restart of a SQL server instance.