When we define a table with Identity column, SQL Server might cache the identity values to improve performance. Due to server restart or database failure some assigned values may be lost. If your application required to have consecutive values, then having identity column was the right choice prior to SQL Server 2017.
You need to write your own mechanism to generate key values without any gap. Using sequence with NOCACHE option we can limit the gaps to transactions that are never committed or by enabling trace flag 272 we can achieve this.
Now in SQL Server 2017 we can able to disable the identity cache at the database level using Alter Database scoped configuration.
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
This option is only available through command only. Now let’s see how this feature work.
First we create a table having an identity column and populate some records in it.
CREATE TABLE Table1( EmpID int identity, EmployeeName varchar(100))
INSERT INTO Table1 ( EmployeeName)
VALUES ( 'AAAAAA'), ('BBBBBB'), ('CCCCCC')
We have now 3 records inserted and the last identity value generated here is 3.
Now we insert a record under a explicit transaction.
BEGIN TRAN INSERT INTO Table1 ( EmployeeName) VALUES ( 'DDDDDD')
Now without commit or rollback, shutdown the SQL Server. We can shutdown the SQL Server from configuration manager or from SSMS or using SHUTDOWN command.
Now insert a record to Table1 and read the table, you will see a gap.
INSERT INTO Table1 ( EmployeeName)
VALUES ('DDDDDD')
You can see empid 4 is missing.
We will repeat all above step but before that we will disable the Identity_Cache.
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
First drop the table Table1 before repeating all of the above steps.
Once you follow all of the above steps and read the table Table1, you will get rows without any gap.
Now in SQL Server 2017 just by disabling Identity cache option we can generate consecutive identity numbers without any gap.
