Solution for Jumping IDENTITY Value in SQL SERVER

In SQLServer, while instance is restarted, sometimes Table’s IDENTITY value is jumped. Jumping value depends on the column data type such as INT [1000], BIGINT [10000]. This will create serious issue on client side applications especially during serial number generation.

In order to avoid this issue, let’s use the below mentioned script which can be called before insertion of data into the specific table.

EXEC USP_FIX_JUMPING_IDENTITY_VALUE [@Table_Name], [@Identitiy_Column_Name]

Script

CREATE PROCEDURE USP_FIX_JUMPING_IDENTITY_VALUE

(

               @Table_Name VARCHAR(100),

               @Key_Column VARCHAR(100)

)

AS

BEGIN

                             

DECLARE         @MaxID INT,

                              @Sql NVARCHAR(1000)

SET @Sql =

                              'SELECT                          

                              @MaxID = ISNULL(MAX([' + @Key_Column + ']), 0)

                              FROM

                              [' + @Table_Name + ']'

EXECUTE sp_executesql  @Sql, N'@MaxID INT OUTPUT', @MaxID=@MaxID OUTPUT

SET @Sql          ='DBCC CHECKIDENT (''[' + @Table_Name + ']'', RESEED, ' + CONVERT(VARCHAR(100), @MaxID) + ')'

EXEC(@Sql)

END