As a SQL Server blogger, I often receive interesting emails and comments from users. Recently, I received an email from the DBCore Group regarding a blog post I had written about catching errors while inserting values into a table. They pointed out an issue with the code I had provided, specifically related to the behavior of the identity column.

In their email, the DBCore Group mentioned that when executing the code I had provided, the identity value of the table was automatically increased even when an error occurred. They believed that when errors are caught, the identity value should not be incremented as there was no real insert. They also mentioned that using the ROLLBACK statement did not have any impact on the identity value.

After researching the issue, the DBCore Group came up with a solution to reset the identity value upon error or ROLLBACK. They shared their code with me, and I would like to discuss it here.

CREATE TABLE SampleTable (
    ID INT IDENTITY (1, 1),
    Col VARCHAR(10)
)

-- Select Identity
SELECT IDENT_CURRENT('SampleTable')

-- Reset Identity Code
BEGIN TRY
    DECLARE @IdentityValue BIGINT
    SELECT @IdentityValue = IDENT_CURRENT('SampleTable')
    
    INSERT INTO SampleTable (Col)
    SELECT 'FourthRow'
    UNION ALL
    SELECT 'FifthRow'
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
    DBCC CHECKIDENT('SampleTable', RESEED, @IdentityValue);
END CATCH

-- Select Identity
SELECT IDENT_CURRENT('SampleTable')

-- Clean up
DROP TABLE SampleTable

While the code provided by the DBCore Group may work in certain scenarios, there are a few considerations to keep in mind.

Concurrency: The code may have issues with concurrency. If multiple processes are accessing the code at the same time, the identity value retrieved may not be accurate, leading to data integrity issues. Locking the table to prevent concurrency can complicate things and degrade performance.

Dependence on Identity Column: It is generally not recommended to depend on the identity column for display purposes. If you need a serial number for display, it is better to create a separate column with an int or bigint datatype and increment it at every insert. Using the identity column for display purposes can result in gaps in the values, which may require additional business logic to handle.

In conclusion, while the code provided by the DBCore Group offers a solution to reset the identity column upon error or ROLLBACK, it is important to consider the potential issues with concurrency and the dependence on the identity column. It is advisable to follow best practices and avoid relying on the identity column for display purposes or sequence-dependent operations.

I appreciate the efforts of the DBCore Group in researching and attempting to resolve the issue. Their code may be useful in certain scenarios, but it is important to carefully evaluate its suitability for your specific requirements.