SQL SERVER – Identity Jumping 1000 – IDENTITY_CACHE

G. Set IDENTITY_CACHE

disables the identity cache.
-- Case  IDENTITY_CACHE = OFF

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
GO


Create a following stored procedure in master DB.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ResetTableNameIdentityAfterRestart]
AS
BEGIN

begin TRAN
    declare @id int = 0
    SELECT @id =  MAX(id) FROM [DatabaseName].dbo.[TableName]
    --print @id
    DBCC CHECKIDENT ('[DatabaseName].dbo.[TableName]', reseed, @id)
Commit

END
Then add it in to Start up by using following syntax.

EXEC sp_procoption 'ResetOrderIdentityAfterRestart', 'startup', 'on';