Disabling the identity cache in SQL Server 2017

One of the changes that’s been brought into the database engine in SQL Server 2017 is the ability to disable the cache that SQL keeps on identity values by using the ALTER DATABASE SCOPED CONFIGURATION command.

What disabling the cache will do is prevent any gaps in the IDs happening if there is an unexpected restart or a failover to a secondary database.


So a caveat to this, I had to play around a bit to get this to work correctly. This is why I drop and recreated the table in the demo below.

If you don’t drop the table and continue to add rows with the cache disabled you’ll need to run the CHECKPOINT command immediately after the ALTER DATABASE statement (otherwise there’ll be a gap in the IDs when the more rows are inserted after the restart).

I’m not entirely sure why this is needed but when I was looking into this I came across this connect item so it seems that odd behaviour around uncontrolled shutdowns and identity columns isn’t unusual. I think I know what’s going on but I don’t want to post anything here that’s incorrect so I’ll just say I’m not sure.

If anyone out there knows why the CHECKPOINT is needed, please let me know!


Anyway, let’s run through a demo showing the difference in behaviour (btw, I’m running this in SQL Server 2017 CTP 2.1.).

First, let’s remind ourselves about the old behaviour so create a test database: –

USE [master];
GO

DROP DATABASE IF EXISTS [TestDB];
GO

CREATE DATABASE [TestDB];
GO

Then create a test table and insert 5 rows: –

USE [TestDB];
GO

CREATE TABLE [dbo].[TestTable]
(PKID INT IDENTITY(1,1) PRIMARY KEY,
 ColA DATETIME);
GO

INSERT INTO [dbo].[TestTable]
(ColA)
VALUES
(GETUTCDATE())
GO  5

Now we’re going to insert another 5 rows within a transaction but we’re not going to commit: –

BEGIN TRAN

INSERT INTO [dbo].[TestTable]
(ColA)
VALUES
(GETUTCDATE())
GO  5

In another query window run the following to simulate a uncontrolled shutdown of the SQL instance: –


SHUTDOWN WITH NOWAIT

Once that’s completed, immediately restart the SQL instance via SQL config manager and open up a new query window and run the following: –

USE [TestDB];
GO

INSERT INTO [dbo].[TestTable]
(ColA)
VALUES
(GETUTCDATE())
GO  5

Now we can check the data in the table: –


SELECT * FROM [dbo].[TestTable]
GO

As expected, there’s a gap in the IDs. Why it jumps to 1002 is discussed in the connect item.

OK, now let’s try running the same code again but this time we will disable the identity cache.

So, reset the demo: –

USE [TestDB];
GO

DROP TABLE IF EXISTS [dbo].[TestTable];
GO

CREATE TABLE [dbo].[TestTable]
(PKID INT IDENTITY(1,1) PRIMARY KEY,
 ColA DATETIME);
GO

OK, now disable the identity cache (leaving the CHECKPOINT there if you want to try without dropping the table): –

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
--CHECKPOINT
GO

The change can be viewed in the following DMV: –

SELECT * FROM sys.database_scoped_configurations;
GO

OK, now that the cache is disabled we can run the test again: –

INSERT INTO [dbo].[TestTable]
(ColA)
VALUES
(GETUTCDATE())
GO  5

And same transaction again, insert 5 rows but do not commit: –

BEGIN TRAN

INSERT INTO [dbo].[TestTable]
(ColA)
VALUES
(GETUTCDATE())
GO  5

Simulate the uncontrolled shutdown: –


SHUTDOWN WITH NOWAIT

Last thing is to restart the SQL instance and run the INSERTs: –

USE [TestDB];
GO

INSERT INTO [dbo].[TestTable]
(ColA)
VALUES
(GETUTCDATE())
GO  5

Now we can check the data in the table: –


SELECT * FROM [dbo].[TestTable]
GO

This time no gaps; we have an uninterrupted sequence in the ID column. So now we have an option for preventing gaps in an identity field without having to use trace flag 272 or setup a sequence manually.

Thanks for reading!

4 thoughts on “Disabling the identity cache in SQL Server 2017

  1. I understand the concerns but I think people expect more than what IDENTITY promises, as revealed by the connect comments. IDENTITY has always promised an identity – it has never promised gapless monotomical increments. Reseeds and rollbacks thwart the latter. Caching for performance reasons made that lack of a promise more painfully obvious. So the question now becomes: Without caching and still with no guarantee of gapless monotomical increments, does performance now suffer?

    Instead of IDENTITY, perhaps ROW_NUMBER fits people’s needs better. And with IDENTITY defined on NUMERIC(38,0), the domain might better meet a design need. I realize neither suggestion will help legacy designs that have incorrectly relied upon behavior that was never documented or promised (no matter how many successes have been observed). But I think new designs should not rely upon IDENTITY to guarantee a ROW_NUMBER.

    • No I think the problem is people actually expect IDENTITY to do what it DOES promise. That is, to ALWAYS increment the next instance of the Identity field by the INCREMENT value. Plain and simple. Yes I understand why it’s not doing it correctly; Why the bug exists, but not why people insist it’s not a bug. It definitely says in the description the increment is used to generate the next value; not that it is “usually used”, but may occasionally throw in an extra random number of up to 1000 (for int). That is Not doing what the description says and therefore IS a bug.

      • I have never see that promise documented, from 4.2 to present. Due to concurrency, gaps have always been possible and are easy to produce. I agree that the connect link is claiming the observed behavior is a bug, but that implies the behavior is contrary to Microsft’s documented behavior (which I have never seen) or is contrary to required ANSI compliance (also never seen by me).

        I do agree it is annoying behavior for those who have become empirically familiar with its prior cacheless behavor. I do agree that Microsoft introduced this cached behavior without advanced warning. But from the narrow views of the Microsoft developer who created an identity cache and those MS employees who tested the advantages and behavior of it, I believe they ensured identity’s new behavior completely aligns with what I see as the documented behavior of the identity property. If there is Microsoft or ANSI documentation to the contrary, I will stand corrected ;).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s