How Do You Tell What Next Identity Column Will Be?

Question

Is there a tsql query to tell what SQL server identity column value it expects to use for the next row insert?

Edited to add:

I deleted and recreated a table with

[personID] [int] IDENTITY(1,1) NOT NULL

as part of my CREATE TABLE command. I've also attempted to reseed identity columns while removing all information in that table and that hasn't always worked. It got me to wondering if there was a way to see what SQL expected to use for your next identity column number.

Answer 1

You probably want to use SCOPE_IDENTITY not @@IDENTITY to restrict it to the identity value in the current scope. This avoids getting new identity values inserted by triggers into other tables and not the table you just inserted into.

But you can calculate what the next identity value is

SELECT IDENT_CURRENT('mytable') + IDENT_INCR('mytable') FROM mytable

The problem is you aren't guaranteed that is the value. You'd have to have a lock such that other inserts are denied on the table when running it to ensure the value is accurate. Also after you run out of 32 bit integers I don't know what the logic is. I don't know whether it rolls over or fails.

Edit: I just tested this (see below for SQL) and it doesn't return the correct value when there is no data. And reseeding with DBCC CHECKIDENT ('tablename', RESEED, 200) actually resulted in the next value being 201 not 200.

CREATE TABLE willtest (myid integer IDENTITY(1,1), myvalue varchar(255))

SELECT IDENT_CURRENT('willtest') + IDENT_INCR('willtest')

INSERT INTO willtest (myvalue)
VALUES ('1')
INSERT INTO willtest (myvalue)
VALUES ('2')
INSERT INTO willtest (myvalue)
VALUES ('3')
INSERT INTO willtest (myvalue)
VALUES ('4')
INSERT INTO willtest (myvalue)
VALUES ('5')
INSERT INTO willtest (myvalue)
VALUES ('6')
INSERT INTO willtest (myvalue)
VALUES ('7')
INSERT INTO willtest (myvalue)
VALUES ('8')

SELECT IDENT_CURRENT('willtest') + IDENT_INCR('willtest')

DBCC CHECKIDENT ('willtest', RESEED, 200)

SELECT IDENT_CURRENT('willtest') + IDENT_INCR('willtest')

INSERT INTO willtest (myvalue)
VALUES ('200')
INSERT INTO willtest (myvalue)
VALUES ('201')
INSERT INTO willtest (myvalue)
VALUES ('202')
INSERT INTO willtest (myvalue)
VALUES ('203')
INSERT INTO willtest (myvalue)
VALUES ('204')
INSERT INTO willtest (myvalue)
VALUES ('205')
INSERT INTO willtest (myvalue)
VALUES ('206')
INSERT INTO willtest (myvalue)
VALUES ('207')

SELECT IDENT_CURRENT('willtest') + IDENT_INCR('willtest')

SELECT * FROM willtest

DROP TABLE willtest