The IDENTITY_CACHE Option in SQL Server

The IDENTITY_CACHE Option in SQL Server

The IDENTITY_CACHE Option in SQL Server

What is 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.

 

A Simple Example

Let’s see an example that will help you better understand this very useful new feature.

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

Running the Example with IDENTITY_CACHE Enabled (Default)

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

The IDENTITY_CACHE Option in SQL Server - Article on SQLNetHub

Next, we 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:

 

The IDENTITY_CACHE Option in SQL Server - Article on SQLNetHub

 

Running the Example with IDENTITY_CACHE Disabled

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:

 

The IDENTITY_CACHE Option in SQL Server - Article on SQLNetHub

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

Recommended Source of Knowledge: 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:

The IDENTITY_CACHE Option in SQL Server - Article on SQLNetHub

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.

 

Check our Related SQL Server Development Articles:

 

Subscribe to our newsletter and stay up to date!

Check out our latest software releases!

Check out Artemakis’s eBooks!

 

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

Loading...

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

© SQLNetHub

Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server and Software Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks 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). Artemakis's official website can be found at aartemiou.com.