Thursday, 14 November 2013

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