Reputation: 32416
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 :
Upvotes: 1
Views: 1714
Reputation: 2270
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.
Upvotes: 1