Getting the next ID without inserting a row.

Question

Is it possible in SQL (SQL Server) to retrieve the next ID (integer) from an identity column in a table before, and without actually, inserting a row? This is not necessarily the highest ID plus 1 if the most recent row was deleted.

I ask this because we occassionally have to update a live DB with new rows. The ID of the row is used in our code (e.g. Switch (ID){ Case ID: } and must be the same. If our development DB and live DB get out of sync, it would be nice to predict a row ID in advance before deployment.

I could of course SET IDENTITY OFF SET INSERT_IDENTITY ON or run a transaction (does this roll back the ID?) etc but wondered if there was a function that returned the next ID (without incrementing it).

Answer 1

Rather than using an IDENTITY column, you could use a UNIQUEIDENTIFIER (Guid) column as the unique row identifer and insert known values.

The other option (which I use) is SET IDENTITY_INSERT ON, where the row IDs are managed in a source controlled single 'document'.

Answer 2

You can pretty easily determine that the last value used is:

SELECT
    last_value
FROM 
    sys.identity_columns
WHERE
    object_id = OBJECT_ID('yourtablename')

Usually, the next ID will be last_value + 1 - but there's no guarantee for that.

Marc

Answer 3

This is a little bit strange but it will work:

If you want to know the next value, start by getting the greatest value plus one:

SELECT max(id) FROM yourtable

To make this work, you'll need to reset the identity on insert:

DECLARE @value INTEGER

SELECT @value = max(id) + 1 FROM yourtable

DBCC CHECKIDENT (yourtable, reseed, @value)

INSERT INTO yourtable ...

Not exactly an elegant solution but I haven't had my coffee yet ;-)

(This also assumes that there is nothing done to the table by your process or any other process between the first and second blocks of code).

Answer 4

try:

Select IDENT_CURRENT('yourtablename')

Answer 5

Edit:

After spending a number of hours comparing entire page dumps, I realised there is an easier way and I should of stayed on the DMVs.

The value survives a backup / restore, which is a clear indication that it is stored - I dumped all the pages in the DB and couldn't find the location / alteration for when a record was added. Comparing 200k line dumps of pages isn't fun.

I had used the dedicated admin console I took a dump of every single internal table exposed inserted a row and then took a further dump of the system tables. Both of the dumps were identical, which indicates that whilst it survived, and therefore must be stored, it is not exposed even at that level.

So after going around in a circle I realised the DMV did have the answer.

create table foo (MyID int identity not null, MyField char(10))
insert into foo values ('test')
go 10

-- Inserted 10 rows
select Convert(varchar(8),increment_value) as IncrementValue,
   Convert(varchar(8),last_value) as LastValue
from sys.identity_columns where name ='myid'


-- insert another row
insert into foo values ('test')

-- check the values again
select Convert(varchar(8),increment_value) as IncrementValue,
   Convert(varchar(8),last_value) as LastValue
from sys.identity_columns where name ='myid'

-- delete the rows
delete from foo


-- check the DMV again
select Convert(varchar(8),increment_value) as IncrementValue,
   Convert(varchar(8),last_value) as LastValue
from sys.identity_columns where name ='myid'

-- value is currently 11 and increment is 1, so the next insert gets 12
insert into foo values ('test')
select * from foo

Result:
MyID        MyField
----------- ----------
12          test      

(1 row(s) affected)

Just because the rows got removed, the last value was not reset, so the last value + increment should be the right answer.

Also going to write up the episode on my blog.

Oh, and the short cut to it all:

select ident_current('foo') + ident_incr('foo')

So it actually turns out to be easy - but this all assumes no one else has used your ID whilst you got it back. Fine for investigation, but I wouldn't want to use it in code.