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

No, there isn't. You can only find out what the last inserted identity value is through @@IDENTITY.

Curious, what do you need to know the value before for? If you need to know the value before, then I recommend generating the ids yourself. You can do this with an ids table keyed on the table name, or, if you have scalability concerns (and you are using transactions) you can have a table for each table that needs an id which would have the id to be inserted (and subsequently incremented).

Or, you could use a GUID, and you would be able to generate these almost anywhere easily.

Answer 2

Since you seed from 1 and increment by 1 (IDENTITY(1,1)), I'm wondering if you can create a procedure where you can set a variable like "Select @@IDENTITY + 1" or something like that.

Answer 3

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

Answer 4

Use GUID columns for your primary keys. Unless you have billions of records and thousands of requests per second, you probably won't notice the performance difference. But unless you like spending far too much time dealing with stupid issues like this, you will notice the difference in your stress level and life expectancy.

Answer 5

This piece of sql will give you the next identity column value (there are probably many reasons not to repeat this snippet in production code)

declare @nextid int;
declare @previousid int;

begin tran

 insert into dbo.TestTable (Col1) values ('11');
 select @nextid = SCOPE_IDENTITY(); 

rollback tran

 select @previousid = @nextid -1
 DBCC CHECKIDENT('dbo.TestTable', RESEED, @previousid);
 select @nextid

this stackoverflow question gives some extra information - sql-identity-autonumber-is-incremented-even-with-a-transaction-rollback

Answer 6

Justice you rock!

Answer 7

Justice, you really rock, that should be the answer to this question !!