We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now


Current identity value

chunhsin asked
Medium Priority
Last Modified: 2010-03-19
I have a table with a identity column. Sometime I need to delete all the data in the table. How can I get the current identity value (next identity value) with no data in the table? (I am using SQL Server 6.5)?
Watch Question

This was not easy.  I have never seen it documented and you're probably not supposed to know it exists.  But here's the answer.

It is stored inside (it's binary - you'll have to pull it out) the updatestamp column in the sysindexes table for the row with your table name.  

select updatestamp from sysindexes where name = 'mytable'

then you'll have to use bitwise operators to get the value, because this has the next value and the increment seed in a single column.


In the SQL Server Books on line, the updatestamp is the count of change to row or page. When I delete som row in the table, the updatestamp increment too. I don´t know how can I do what you said!

Out of curiosity, why would you need to know the next number if the table is empty?

You can start entering new data without knowing what the next number should be.

If you supply more information, maybe we can figure out a way around your problem.  :)

You might be better getting the value back after an INSERT through the @@IDENTITY variable rather than predicting it beforehand (what if some other user gets in first? - who wins?)

This answer is really secret stuff but I am offering it to you free.

dbcc checkident (tablename)



Sorry for the delay.

My situation is the following:
  I have a application that insert user login information into SQL Server. Everyday, I download the user login information to a log file, and delete from SQL Server. When I start my application I need to calculate an offset to be sum with a control number, and the offset is evaluate from the currente identity value that need to be get using ODBC.

  I can´t  use "dbcc checkident" because this command may reset the current identity value.

Food for thought - When something is this difficult to resolve, the solution often is to rethink your process, not to find a force fit workaround.

When you start your application, could you add a dummy row, select the id value, add 1 to it, delete the dummy row and then do the calc?

It's not the most elegant solution, but I think it would work.  :)

Either that, or you're going to have to store the ID number in another table and have an after insert trigger on the first table that updates that value in the second table.

Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.