Learn SQL Server

The IDENTITY_CACHE Option in SQL Server

by

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

 

Learn more tips like this! Enroll to our Online Course!

Check our online course titled “Essential SQL Server Development Tips for SQL Developers” (special limited-time discount included in link).

Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Essential SQL Server Development Tips for SQL Developers - Online Course
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

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.

 

Learn useful SQL Server Administration tips!

Check our online course on Udemy titled “Essential SQL Server Administration Tips” (discount included in link).

As a SQL Server DBA, you need to maintain secure, performant and efficient SQL Server databases and our course can definitely help you with that.

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

 

Featured Online Courses:

 

Check our Related SQL Server Development Articles:

 

Subscribe to our newsletter and stay up to date!

Subscribe to our YouTube channel (SQLNetHub TV)

Easily generate snippets with Snippets Generator!

Secure your databases using DBA Security Advisor!

Check our latest software releases!

Check our eBooks!

 

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

Loading...

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

© SQLNetHub



Views: 11,197