Home > SQL Server > Finding Next Identity Value, and a Wild Goose Chase.

Finding Next Identity Value, and a Wild Goose Chase.

A question asked on stack overflow was to find the next identity value that would occur on a table, without being required to add a record to work it out. The problem lies in that if the highest row is deleted, the number is not reused so any answers using the existing rows can be incorrect.

Logically the value must be stored, so first place I checked was the DMV’s. That stores the currently used value, but does not store the next value.

The wild goose chase started there…

  • I used the dedicated admin console to pull all the system tables, expecting it to be in sys.syshobtcolumns, no joy. Dumped the whole system table contents before and after an insert looking for the difference and didn’t spot it.
  • Took a dump of every page in the file before and after inserting a new row and having the textural dumps compared in a text comparison application, still no joy.
  • Started dumping out log records using the following script and pulled the identity calls:
select *
from ::fn_dblog(null, null)
where operation = 'LOP_IDENT_NEWVAL'
  • After a couple of hours running around the pages trying to find it, I realised I should of stuck with the DMV and I really went the wrong way around it.

 The DMV has the right answer it seems, but as two fields you have to combine to get 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

MyID        MyField
----------- ----------
12          test      

(1 row(s) affected)

So adding the increment to the last value will predict the next value correctly, assuming someone else does not grab it in the mean time which is why it is not a good idea to use in code, but if you need to investigate a table and want to know what it thinks is next, without actually inserting a row and affecting the table, then it is useful.

All that and the easy way is then:

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

Ah well, was fun investigating it – but what a wild goose chase to find it was an easy answer.

  1. faheem
    January 20, 2010 at 1:13 pm

    very nice

  2. Matthijs
    July 27, 2010 at 9:58 pm

    I just saw your post on stack overflow, and it was good. Found this blog in the same google. Also good is that you worked on answering the question, instead of questioning why the question was asked – if you catch my drift.

    In my own case I was checking up reports from a user on missing records. I could see in the table no gaps in the ID order, so that told me the records had probably not been added and then physically deleted (there’s no ID re-set in this system). But how would I know if the missing records had been added at the “end” of the table or not? I needed to know the next ID, without actually making a record, to know if there was a “gap” at the “end” of the table.

  3. Martin
    May 15, 2011 at 1:14 pm

    The relevant columns in the system tables are sys.syscolpars and idtval, I have successfully (just for “fun”) changed the identity property of columns using these on a test instance in single user mode.

    This doesn’t get updated in real time however. If you were to do

    SELECT * FROM sys.syscolpars WHERE id = OBJECT_ID(‘YourTable’)

    You should see the idtval value change.

  1. No trackbacks yet.

Leave a Reply to Martin Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: