The IDENTITY_CACHE Option in SQL Server

The IDENTITY_CACHE Option in SQL Server

The IDENTITY_CACHE Option in SQL Server

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. However, in the case where an identity column is being populated with values and an unexpected SQL Server restart or failover takes place, then after the process takes place again, there will be a gap between the identity column values before the restart/failover and the values right after the restart/failover.

If however, you disable this option by setting it to OFF, you can avoid gaps in identity column values in cases of unexpected SQL Server restarts or fail-overs.

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.

Let’s see an example.

First, let’s create a demo database and a table with an identity column named “id”.

CREATE DATABASE DemoDB;
GO

USE DemoDB;
GO

CREATE TABLE tblIDTest(
id INT IDENTITY(1,1) PRIMARY KEY,
code varchar(20)
);
GO

Now, let’s insert 5 records and check the table contents:

--Insert some sample data
INSERT INTO tblIDTest (code)
VALUES ('code1'),('code2'),('code3'),('code4'),('code5');
GO

--Check identity values
SELECT * FROM tblIDTest;
GO

SQLNetHub Article: The IDENTITY_CACHE Option in SQL Server

Next, let’s try to insert another 5 records but before commit them we simulate an unexpected server shutdown:

BEGIN TRAN 

--Insert some sample data
INSERT INTO tblIDTest (code)
VALUES ('code6'),('code7'),('code8'),('code9'),('code10');
GO

--You will have to execute 2 times the SHUTDOWN command for this example
SHUTDOWN WITH NOWAIT; 
GO

After restarting the instance we try again the insert the above 5 records:

--Insert some sample data
INSERT INTO tblIDTest (code)
VALUES ('code6'),('code7'),('code8'),('code9'),('code10');
GO

--Check identity values
SELECT * FROM tblIDTest;
GO

As you can see in the results below, there is a gap in the identity values:

 

SQLNetHub Article: The IDENTITY_CACHE Option in SQL Server

Now, let’s re-run the scenario but right before we start, let’s disable the IDENTITY_CACHE option for our demo database:

USE DemoDB;
GO

ALTER DATABASE SCOPED CONFIGURATION
SET IDENTITY_CACHE = OFF;
GO

OK, here we go again. Let’s truncate the sample table and insert again the first 5 records and check the results:

 

SQLNetHub Article: The IDENTITY_CACHE Option in SQL Server

So far, so good. All 5 records have correct identity values.

Recommended for you: My new eBook titled “Developing with SQL Server

Now, like in the first example, let’s try to insert another 5 records but before commit them we restart the SQL Server instance:

BEGIN TRAN 

--Insert some sample data
INSERT INTO tblIDTest (code)
VALUES ('code6'),('code7'),('code8'),('code9'),('code10');
GO

--You will have to execute 2 times the SHUTDOWN command for this example
SHUTDOWN WITH NOWAIT;
GO

Now, right after the simulated “unexpected server shutdown”, let’s start SQL server and try again to insert the second set of 5 records and check the identity values to see if there are any gaps:

SQLNetHub Article: The IDENTITY_CACHE Option in SQL Server

As you can see, this time, the sequence of identity values is continuous, even though there was an unexpected server shutdown.

This was an example of the IDENTITY_CACHE option’s usage in SQL Server 2017.

I hope you enjoyed the article!

More new features of SQL Server 2017 will be covered in subsequent articles.

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (9 votes, average: 5.00 out of 5)

Loading...

Check also: What’s new in SQL Server 2017 Database Engine

Reference: SQLNetHub (https://www.sqlnethub.com)

 


Recommended eBooks on SQL Server:

Developing with SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Developing SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.