Asking a Microsoft SQL Server database for the next auto-generated identifier on a table

Question

I have a table in a SQL Server database that has an auto-generated integer primary key. Without inserting a record into the table, I need to query the database and get what the next auto-generated ID number will be.

I think it's SQL Server version 2005, if that makes a difference.

Is there a way to do this?

Answer 1

Yes, but it's unreliable because another session might use the expected number.

If you still want to do this, use IDENT_CURRENT

Edit, as the comments have pointed out (improving my answer):

• you need to add one IDENT_INCR('MyTable') to this to get the potential next number
• another process may rollback and this number may not be the one used anyway

Answer 2

No, there is not. The ID will only ever be defined and handed out when the actual INSERT happens.

You can check the last given ID by using

DBCC CHECKIDENT('YourTableName')

but that's just the last one used - no guarantee that the next one is really going to be this value + 1 - it could be - but no guarantees

Answer 3

This is pretty much a bad idea straight off the bat, but if you don't anticipate high volume and/or concurrency issues, you could just do something like this

select @nextnum = max(Id) + 1 from MyTable

Answer 4

I don't think thats possible out of the box in MS SQL (any version). You can do this with column type uniqueidentifier and using function NEWID(). For int column, you would have to implement your own sequential generator.

Answer 5

The only way to get a number that is guranteed not to be used by another process (i.e., a race condition) is to do the insert - is there any reason you can't do a NULL insert (i.e., just insert into the table with NULLs or default values for all other columns) and then subsequently UPDATE it?

i.e.,

CREATE TABLE bob (
  seq INTEGER IDENTITY (1,1) NOT NULL,
  col1 INTEGER NULL
)
GO
DECLARE @seqid INTEGER

INSERT INTO bob DEFAULT VALUES
SET @seqid = SCOPE_IDENTITY()
-- do stuff with @seqid
UPDATE bob SET col1 = 42 WHERE seq = @seqid
GO

Answer 6

You shouldn't use the technique in code, but if you need to do it for investigative purposes:

select ident_current(‘foo’) + ident_incr(‘foo’)

That gives you the last value generated + the incrementation for the identity, so should represent the next choice SQL would make without inserting a row to find out. This is a correct value even if a rollback has pushed the value forwards - but again, this is investigative SQL not stuff I would put in code.

The two values can also be found in the sys.identity_values DMV, the fields are increment_value and last_value.

Answer 7

Another way, depending on what your doing, is inserting whatever data goes into the table, and then using @@identity to retrieve the id of the record inserted.

example:

declare @table table (id int identity(1,1), name nvarchar(10))

insert into @table values ('a')
insert into @table values ('b')
insert into @table values ('c')
insert into @table values ('d')

select @@identity

insert into @table values ('e')
insert into @table values ('f')

select @@identity