Sql-server – How to increment identity without add a row and get the skipped value

identitysql server

I'm trying to preserve value in identity column in manner to be added later. The value should be used in implicit foreign key when there is no constraint.

For example:

I have this table:

Create table [t](
   [id] [int] IDENTITY(1,1) NOT NULL,
   [value] [varchar](255) Not NULL
)

I want to increment the id without adding line, and get the id, (similar to oracle sequence)

I'm trying to read current sequence value:

Select IDENT_CURRENT('t') as id

result:

id
----
1

trying to increment, use insert with default values:

insert into t default values

result:

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'value', table test.dbo.t; column does not allow nulls. INSERT fails.
The statement has been terminated.

I'm trying to read current sequence value:

Select IDENT_CURRENT('t') as id

result:

id
----
2

Means the sequence increment had been succeeded, even though no row was added.

Now I need a way to read the skipped value in a "scope safe" way.

SCOPE_IDENTITY and @@IDENTITY doesn't been changed unless the insertion was succeeded, and IDENT_CURRENT('t') is not "scope safe".

Does anyone have any idea how can I do so?
I prefer a solution that I can use for any table, not limited for a specific table (for specific table: like insert full row and delete it – I need to know the column names).

Best Answer

You could use DBCC CHECKIDENT (RESEED) for this purpose, but keep in mind you need special permissions, and I'm not sure it worth.

Permissions
Caller must own the schema that contains the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

CREATE TABLE tbl (foo int IDENTITY, bar int);

INSERT INTO tbl (bar) VALUES (10),(15),(20);

SELECT * FROM tbl;

foo         bar
----------- -----------
1           10
2           15
3           20    

/* save current identity value */
DECLARE @CURRENT_ID int;
SELECT @CURRENT_ID = IDENT_CURRENT('tbl');
PRINT 'Current ID = ' + CAST(@CURRENT_ID as varchar(10));

Current ID = 3

/* get next identity value */
DECLARE @NEXT_ID int;
SET @NEXT_ID = @CURRENT_ID + 1;

/* reseed */
DBCC CHECKIDENT (tbl, RESEED, @NEXT_ID);

SELECT * FROM tbl;

foo         bar
----------- -----------
1           10
2           15
3           20
5           30
6           35
7           40


DROP TABLE tbl;
Related Question