Reseeding Identity Column

I received a project that involves inserting thousands of rows into a table with an identity column as its Primary Key. The concern is, with a failed transaction, there is a possibility of significant gaps within the sequence of the column. To handle the requirement, I grab the last identity value right after the transaction starts. This will also lock the table until the transaction is committed or rolled-back. Paired with the TRY..CATCH statement, I was able to rollback the transaction and reseed the table as if the insert did not happen and preserve the sequence of the identity column. I was able to use this technique since I am aware no one else is using the table as this transaction is being executed. If the table is updated frequently, transaction must be handled properly so as not to lock the table longer than necessary.
Here's the pseudo-code:

begin transaction

begin try
set @last_identity_value = ident_current(tablename)
set @last_identity_value =
 case when @last_identity_value = 1 then 0
      else @last_identity_value
 end

/*insert the records to table here*/

commit transaction
end try
begin catch
 rollback transaction
 DBCC CHECKIDENT (tablename,RESEED,@last_identity_value)
end catch

As always, comments are welcome...


~~ CK

How to Find my Missing Identity

IDENTITY is a property of a numeric data type that tells SQL Server it is an auto-incrementing column. It increments every time a new record is inserted to the table.

An identity column can not start with value less than the seed and will always increment by the identity increment property. Although an identity column may be unique, it is not always sequential. If a record is deleted from the table, the deleted value will not be reuse, even if it's the last record.

Because it's not always sequential, it's possible that there are missing value on the column that breaks the sequence. Here's a query that could check for the missing sequence in an identity column:

select a.IdentityColumn+1
from TableName A
left join TableName B on A.IdentityColumn+1 = B.IdentityColumn
where B.IdentityColumn is null

~~CK