Question about getting the latest identity field in a specific table

[Benny]

Dear All,

Suppose in the program a record is added to a table whose
primary key is a identity field. If I really want to get the lastest
value for that field after the insertion, is it the best way to use
IDENT_CURRENT() to obtain this value?

Thanks for your kind attention

Yours faithfully,
Benny

[Anith Sen]

I would rather use SCOPE_IDENTITY() or @@IDENTITY depending on the
requirements. SQL Server Books Online states that, IDENT_CURRENT is similar
to the 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three
functions return last-generated identity values. However, the scope and
session on which 'last' is defined in each of these functions differ.

- IDENT_CURRENT returns the last identity value generated for a specific
table in any session and any scope.
- @@IDENTITY returns the last identity value generated for any table in the
current session, across all scopes.
- SCOPE_IDENTITY returns the last identity value generated for any table in
the current session and the current scope.

--
- Anith
( Please reply to newsgroups only )

[Erland Sommarskog]

Anith Sen (an***@bizdatasolutions.com) writes:

- IDENT_CURRENT returns the last identity value generated for a specific
table in any session and any scope.

One important thing to clarify here is that IDENT_CURRENT() can be affected
by insertions by other processes, where as scope_identity and @@identity
cannot.

Thus, ident_current() is rarely the function you should call in application
code.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp