Search code examples
sqlsql-serverauto-increment

Regenerate lost identity value

Is there a way to recreate the identity value of a SQL Server table if the statements failed inside a transaction block?

Please go through the code below:

DECLARE @IdentityTable AS TABLE (ID INT IDENTITY(1, 1), Description VARCHAR(50))

INSERT INTO @IdentityTable (Description) 
VALUES('Test1')

BEGIN TRY 
BEGIN TRANSACTION IdentityTest
    INSERT INTO @IdentityTable (Description) 
    VALUES('Test2')

    INSERT INTO @IdentityTable (Description) 
    VALUES(1/0)

    COMMIT TRANSACTION IdentityTest
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION IdentityTest
END CATCH

INSERT INTO @IdentityTable (Description) 
VALUES('Test4')

SELECT * FROM @IdentityTable

Result

Identity No 3 is lost due to ROLLBACK TRANSACTION. Is it possible to regain it?

Solution

  • You're trying to use the IDENTITY property to generate consecutive numbers, and maintain it; that isn't what IDENTITY is for. It's designed to provide an incrementing value based on the current seed (on it's own (without a PRIMARY KEY constraint or UNIQUE INDEX), it doesn't even guarantee uniqueness as the seed could be changed (thanks HoneyBadger for reminding me so early in the morning)).

    If an INSERT fails, the value of the IDENTITY will still be incremented. Also, if you were to DELETE a row from a table, that would not cause every "latter" row to have their ID's updated accordingly; thus you would also have a gap then.

    The only guaranteed way of ensuring you get an incrementing value is by using a function like ROW_NUMBER at run time. For example:

    SELECT ROW_NUMBER() OVER (ORDER BY ID) AS cID,
       Description
FROM YourTable;

    The Remarks section of the documentation specifically states that consecutive values are not guarenteed:

    Identity columns can be used for generating key values. The identity property on a column guarantees the following:

    ...

    Consecutive values within a transaction – A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table. If values must be consecutive then the transaction should use an exclusive lock on the table or use the SERIALIZABLE isolation level.

    Consecutive values after server restart or other failures – SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.

    Reuse of values – For a given identity property with specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again. This can result in gaps when the subsequent identity values are generated.