I have been blogging for almost 8 years now. I believe I have seen pretty much every single kind of email and comments I can receive from users. However, there are sometimes few which just are so interesting that I feel like blogging about it. This blog post is written based on the earlier blog post which I have written over here How to Catch Errors While Inserting Values in Table. I suggest you read that blog post before continuing this post. Now in the original blog post I have mentioned how one can catch the error with the help of TRY…CATCH. In reply to this blog post, I received an email which I have reproduced after removing few of the unimportant details.
“Hi Pinal,
We read your blog post How to Catch Errors While Inserting Values in Table and we have found one error in your blog post. The error is that when we execute your query displayed, it automatically increases the identity value of your table. In another word here is what is happening.
We believe that when errors are caught, it should be not increase the identity of the table.
We immediately removed the TRY…CATCH error and our result was the same. That means TRY…CATCH is just to display the error gracefully. After carefully reading your blog post we realized that you had mentioned the same there. However, we still believe the identity should not be incremented as there was no real insert.
After carefully thinking we decided to use ROLLBACK. However, the ROLLBACK even does not have any impact on the IDENTITY. I think this is indeed not a good thing.
We finally researched a lot on web and found ROLLBACK does not impact identity. Finally, we decided to take up this challenge and resolve the problem. We came up with following code.
This works beautifully and resets the identity upon error or ROLLBACK. Please review the code and give us your opinion about the same.
Please post this on your blog as I believe this will be useful to many who are facing similar issues.
Thanks,
DBCore Group of (CompanyName Removed)”
Here is the code which was included along with the email.
CREATE TABLE SampleTable (ID INT IDENTITY(1,1), Col VARCHAR(10))
GO
-- Select Identity
SELECT IDENT_CURRENT('SampleTable')
GO
-- 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
GO
-- Select Identity
SELECT IDENT_CURRENT('SampleTable')
GO
-- Clean up
DROP TABLE SampleTable
GO
Very interesting email indeed. First of all, I really appreciated the email composed by the DBCore Team. They did some real research on the subject and attempted a solution. Here is my comment about the above code.
Comment1: First of all, above code may have issue with concurrency. That means, after you have retrieved identity value from the table, it is quite possible that in any other process with similar or different code the identity would have been updated and when you reset the identity at that time, you may reset it incorrect value and eventually forfeiting the original purpose of the identity. If you have Primary Key or any other kind of Unique Key, you may start facing error as well and your data integrity would have been compromised.
Here you may come up with the solution that when you enter this transaction you put the lock on the table, but that will additionally complicate the things and your performance will degrade big time. This code will work in the case, when you have single transaction at any point of time accessing the code and there is no issue with concurrency and performance.
Comment2: There should be never any dependance on the identity column of the table. If you need a serial number generated for display purpose, just create a column with int or bigint dataype and increment it at every insert. If you are using an identity column value for display, you should be very much aware of the situation that you will have gaps in that value and you should be able to write business logic around it. For example, you should not use identity columns as order number and if there is a gap in the order number, your business should just accept it. If you are using identity column value as invoice number, I think it is a bad idea as a general rule of accounting says that invoice number should be in sequence.
Well, I guess that is what I wanted to add the code which is displayed above. I would personally follow my both the comments above and will not use identity columns for any display purpose or will not depend on sequence of it. However, I truly appreciate the efforts of DBCore group.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL Tagged: Identity 
