Resolve SQL Server Identity Jumping 1000 Issue, resolve with DENTITY_CACHE

In SQL Server time we faced an issue with the Identity field, It directly jumps with 1000 numbers. So in this article, we resolve this issue.

What to do suppose your last entry put with Jumped value.

  1. Copy your last row data somewhere in notepad
  2. Remove last row
declare @max int
select @max=max([Id]) from [TestTable]
if @max IS NULL
  SET @max = 0
DBCC CHECKIDENT ('[TestTable]', RESEED, @max)

The above code will reset your seed but still, the problem is there. Because the SQL Server stores identity data in cache by default it’s ON so we need to turn it Off using the below code.

USE YourDatabaseName
 
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
GO