retrieving next identity values

This is not a very practical question, but I thought would be a good theoretical exercise.

What I want to do is retrieve the next available identity value for a given table, BEFORE AN INSERT OCCURS. I was trying to write a generic stored procedure that you could supply the table name and the rest would be taken care of.

This idea first came to me while I was trying to find a work around, for a bit of code already written. I have taken a different approach to the problem as this is not practical in a multi-user environment, but I would like to know if it is actually possible...

select max(rowid) from LogFile
select autoval from syscolumns where id = object_id('LogFile') and name = 'rowid'

-----------
24

(1 row(s) affected)

autoval
-----------------------------
0x18000000010000000100000082

(1 row(s) affected)

This leads to the conclusion that autoval contains the necessary information to find out the next value:
0x18 is the hex value for 24,
the following 00001 and 00001 correspond to the Seed and Interval settings. 82 indicates that it is an IDENTITY field.

angelIII, thanx for explaining the autoval field, I had actually looked at that before but I had no idea what it was representing.

The only problem I have with your answer is that it appeared to work the 1st time I tried it, but after I inserted a few more records the '0x18' part of it didn't change. From what you were saying I thought that the beginning of of that hex field should change as I insert more records but it appears that it doesn't.

Am I missing something you have said in your response, or does this happen to you aswell

angelIII, thanx for explaining the autoval field, I had actually looked at that before but I had no idea what it was representing.

The only problem I have with your answer is that it appeared to work the 1st time I tried it, but after I inserted a few more records the '0x18' part of it didn't change. From what you were saying I thought that the beginning of of that hex field should change as I insert more records but it appears that it doesn't.

Am I missing something you have said in your response, or does this happen to you aswell

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

You can now accept one of the comments in this thread as an answer. To award the other Expert, you can create a new question in this topic area with a title of 'For ExpertName -- 10352216' using that Experts username.

Remember, the Accept Comment as Answer button is in the header of the comment.

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below:
PERCENT_RANK():
PERCENT_RANK() function will returns the percentage value of rank of the values among its group.
PERCENT_RANK() function value always in be…

This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.