CODEWITHSUNDEEP
sqlsql-server-2008dbcc
Brann
Brann

Reputation: 32416

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

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

Answers (1)

Darryl Peterson
Darryl Peterson

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

Related Questions