SQL Server 2008 – Get the next Identity Value

Question:
How to get the next Identity Value in SQL Server 2008?

Answer:
It is very simple to the get the identity value in SQL Server. Let’s see with the example below

Lets‘s create some dummy data as mentioned below …

01 SQL Server 2008 - Get the next Identity Value

SQL Server 2008 – Get the next Identity Value

In SQ Server, there are two functions IDENT_CURRENT and IDENT_INCR. With the help of these funtions, we can get next value Identity.

IDENT_CURRENT:
It returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.

IDENT_INCR:
It Returns the increment value (returned as numeric (@@MAXPRECISION, 0)) specified during the creation of an identity column in a table or view that has an identity column.

Get the Last Identity Value as below…

02 SQL Server 2008 - Get the next Identity Value

SQL Server 2008 – Get the next Identity Value

Get the Identity Increment

03 SQL Server 2008 - Get the next Identity Value

SQL Server 2008 – Get the next Identity Value

Get the next Identity Value

04 SQL Server 2008 - Get the next Identity Value

SQL Server 2008 – Get the next Identity Value

Hope this help you.

2 comments

  • Wilf

    That is not a save way. If you get the value and you don’t use it immediately after getting it, another user of the same database may have used it already before you ! The only way to RESERVE the value seems to be an INSERT, getting the Current identity, then DELETE the new record; the id will then be “reserved”. Then use identity_insert on to insert your new record using the given ID.