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.

Solution 1

No, there isn't any guaranteed way (although you can certainly find out what the next value might be, another command might go and use it before you can make any use of it). The only guaranteed value you can retrieve is the previously inserted identity value through SCOPE_IDENTITY() (which will return the identity value last generated for the current scope).

It's questionable what purpose why one would need to know the value before (when using an automatically incremented seeded identity column).

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 an id 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 easily generate these on the client side before sending it to your database.

Solution 2

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

Solution 3

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

Comments

[MarlonRibunal]

At this point we cannot even decide if SCOPE_IDENTITY is more appropriate than @@IDENTITY. Or if these are applicable at all.

[Will Rickards]

That part was responding to casperOne's suggestion. Now that the question has been edited - the real issue is more interesting.

[Can Sahin]

I beg to differ; performance issues can occur even when there are about a hundred thousand records and very few requests. See this question, for example: stackoverflow.com/questions/1703303/… . We moved everything from identities to GUIDs a few years ago and we are starting to regret this move...

[ZygD]

Now, don't use GUID as a PK unless you 1. Know what you're doing 2. Don't make it clustered (see point 1)

[ZygD]

cool, FYI: blog.sqlauthority.com/2007/03/25/…

[Matt]

you don't need FROM mytable in your first query

[Matt]

It also doesn't account for someone having intentionally changed the next seed number, which is what I happen to be interested in today.

[Matt]

doesn't actually answer the question

[Niraj]

I am not sure if this is specific to a database version but I am SQL Server 2008 R2, and mine worked well, it honored DBCC reseed, and gave me the correct values. @HerbalMart It works based on Ident_Current, and gives you correct values even if you have removed some rows.

[Niraj]

This command fails only when you have created a table and not inserted a record in this, it will give you 2 instead of 1

[Niraj]

This command fails only when you have created a table and not inserted a record in this, it will give you 2 instead of 1