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 …
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…
SQL Server 2008 – Get the next Identity Value
Get the Identity Increment
SQL Server 2008 – Get the next Identity Value
Get the next Identity Value
SQL Server 2008 – Get the next Identity Value
Hope this help you.
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.
Hi Wilf,
Let me clear, the purpose of this post to give the brief intro about IDENT_CURRENT and IDENT_INCR functions with example. I have not mentioned anywhere to use these functions ( IDENT_CURRENT and IDENT_INCR) for inserting the new identity value.
You can refer my other post to insert the value to identity column : http://varindersandhu.in/2011/02/01/add-the-value-into-an-identity-column-in-sql-server/