How to get the next identity value from SQL Server

Question

I need to get the next identity value from SQL Server.

I use this code :

SELECT IDENT_CURRENT('table_name') + 1 

This is correct, but when the table_name is empty (and next identity value is "1") returned "2" but result is "1"

Answer 1

I think you'll want to look for an alternative way to calculate the next available value (such as setting the column to auto-increment).

From the IDENT_CURRENT documentation, regarding empty tables:

When the IDENT_CURRENT value is NULL (because the table has never contained rows or has been truncated), the IDENT_CURRENT function returns the seed value.

It doesn't even seem all that reliable, especially if you end up designing an app that has more than one person writing to the table at the same time.

Be cautious about using IDENT_CURRENT to predict the next generated identity value. The actual generated value may be different from IDENT_CURRENT plus IDENT_INCR because of insertions performed by other sessions.

Answer 2

In case when your table will be empty then this query will work perfectly.

SELECT   CASE     WHEN (SELECT         COUNT(1)       FROM tablename) = 0 THEN 1     ELSE IDENT_CURRENT('tablename') + 1   END AS Current_Identity;