IDENTITY is widely used for generating the sequential value mainly for primary key. In some cases like failure of database or restarting of database might result in gap between the sequential value. Today we will have hands on experience on the IDENTITY Cache, it's limitation and way around.
What is IDENTITY Cache
SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert.
Hands on Lab
-- At first, make sure that IDENTITY_CACHE is set ON.
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = ON
GO
IF OBJECT_ID('Test1', 'U') IS NOT NULL
DROP TABLE Test1;
GO
-- Create the table
CREATE TABLE Test1(
pk_id int not null identity(1,1),
name varchar(10) default ('Mehedi')
)
GO
-- Populate with 10 sample data
INSERT INTO Test1 default values
GO 10
-- Examine the data
SELECT * FROM Test1
GO
BEGIN TRANSACTION
Now open a new query window and execute below query:
SHUTDOWN WITH NOWAIT
Now insert couple of rows.
INSERT INTO Test1 default values
GO 5
We can see that the 20th row's value is 20 where as the 21st row has 1002. SQL Server makes this gap for ensuring performance.
Drawbacks:
This gap may not acceptable in some cases. For example, replication with heterogeneous databases.
Way around
There are two way around.
1. Disabling IDENTITY_CACHE
We can disable the IDENTITY_CACHE. In that case sudden restart of the SQL Server or database crash will not result in gap in the identity value.
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
GO
However, there is a very little performance hit is associated with it which might be safely ignored if there is no additional CPU or Memory Issues.
2. Disabling the IDENTITY_INSERT
We can disabling the IDENTITY_INSERT at insert time to fill the gap.
-- Disable the IDENTITY column and re-enter
SET IDENTITY_INSERT Test1 ON
INSERT INTO Test1(pk_id,name) VALUES(88, 'Hasan')
SET IDENTITY_INSERT Test1 OFF
For more details on this, please check my previous blog.
Hope it was useful to you!