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