How can I increment an identity column without inserting a value ?

Question

First, I'm aware of this question which didn't get answered because what the OP was really trying to do was'nt incrementing an identity column

---

I've got an identity column with a current seed value of x, and I would like to reseed it to x+1 (ie I want my identity column to jump directly from x to x+2.

I know I can do that using the following command

create procedure IncrementSeedValue
(
@TableName varchar(255),
@IncrementValue int
)
as

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
    declare @v bigint
    select @v = IDENT_CURRENT(@TableName)+@IncrementValue
    DBCC CHECKIDENT (@TableName, RESEED, @v )
COMMIT TRANSACTION;
go

However, I've got a few questions :

• Is the isolation level "serializable" adequate here?
• Would that lead to problem if I'm using SQL Server mirroring
• Are there other pitfalls I should be aware of ?

Answer 1

An IDENTITY value is consumed for every INSERT.

I would either INSERT a value and immediately DELETE it, or INSERT the value and issue a ROLLBACK.

In either case, the IDENTITY value will be consumed.

I don't feel that DBCC commands should be used in "normal" code and should be reserved for administrative tasks only.

Or, if you can use SET IDENTITY_INSERT ON and calculate what the next value with the skipped value should be.