Scope Identity in SQL Server

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch.

Premalatha
Software Engineer

Sridevi, if this helps please login to Mark As Answer. | Alert Moderator

Hi

It returns identity of Last Record Inserted in the table

R D Patel

Sridevi, if this helps please login to Mark As Answer. | Alert Moderator

Hi,
you will get the Latest identity value of the current scope which you are in.
for example:
if you are executing an SP. in that sp you had inserted a record in a table A and the identity value is 10 and also at the same time from the outside another user insert the record in the same table as 11.
if used the scope identity you will get the value as 10.

Tammana

Sridevi, if this helps please login to Mark As Answer. | Alert Moderator

Hi Sridevi,

read this blog clearly exaplined about the Scope Identity

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

If this helps you .
Please "Mark as Answer"

Sridevi, if this helps please login to Mark As Answer. | Alert Moderator

Refer this url
http://www.java2s.com/Tutorial/SQLServer/0300__Sequence-Indentity/UsingtheSCOPEIDENTITYFunction.htm

Sridevi, if this helps please login to Mark As Answer. | Alert Moderator

SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()

The above both sql query returns the last identity value entered into a table in your current session.

SELECT IDENT_CURRENT('tablename')
It returns the last IDENTITY value produced in a table

Sridevi, if this helps please login to Mark As Answer. | Alert Moderator

Hi
refer this

http://www.dotnetfunda.com/articles/article516-difference-between-scopeidentity-and-identity.aspx

Thanks n Regards
Bhupendra Tiwari

Sridevi, if this helps please login to Mark As Answer. | Alert Moderator

Hi,
SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

Sridevi, if this helps please login to Mark As Answer. | Alert Moderator

Can u give some example?

Sridevi, if this helps please login to Mark As Answer. | Alert Moderator