SCOPE_IDENTITY, IDENT_CURRENT & @@IDENTITY

SCOPE_IDENTITY, IDENT_CURRENT & @@IDENTITY

@@IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT return last-generated identity values.

IDENT_CURRENT takes one parameter i.e. table name and returns the last identity value inserted in this table.

Example of IDENT_CURRENT

Create a table with identity column and insert values in it:

Create Table emp_dummy
(
ID int IDENTITY (5,2),
Name varchar (20)
)

In the above Create Table command, IDENTITY (5,2) means that Identity values will start from 5 and will increment by 2.

Insert emp_dummy values ('Sachin')
Insert emp_dummy values ('Kumar')

Select * from emp_dummy

ID	Name
5	Sachin
7	Kumar

Select IDENT_CURRENT ('emp_dummy')

will show 7


SCOPE_IDENTITY returns the last identity generated in the current scope. A scope is defined as a module - a stored procedure, function or batch.
@@IDENTITY returns the last identity value generated in the current session.

The difference between SCOPE_IDENTITY and @@IDENTITY is that @@IDENTITY is not limited to current scope but to session and SCOPE_IDENTITY is limited to current scope. A session can have one or more than one scope.

Syntax of SCOPE_IDENTITY is

SCOPE_IDENTITY()

You use SCOPE_IDENTITY like

Select SCOPE_IDENTITY

Syntax of @@IDENTITY is

@@IDENTITY()

You use @@IDENTITY like

Select @@IDENTITY

Few important points on @@IDENTITY, SCOPE_IDENTITY & IDENT_CURRENT:

1. Functions that start with @@ have server-wide scope. @@IDENTITY is always associated with the current session. If two users using two different connections are connected to SQL Server and they both insert a row in a table with identity column, each of them will get the value they just inserted, if they run @@IDENTITY.

2. When your DML statement has affected only one table, and you query IDENTITY value from the 'same connection' that made the modifications, SCOPE_IDENTITY, @@IDENTITY and IDENT_CURRENT will return the same value.

3. If you query SCOPE_IDENTITY and @@IDENTITY from a new connection, they will return NULL. This is because SCOPE_IDENTITY and @@IDENTITY are specific to connection that executed the DML statement that changed the IDENTITY values.


References:
Microsoft SQL Server 2000 programming by example By Fernando G. Guerrero, Carlos Eduardo Rojas.
SQL functions programmer's reference By Arie Jones.
The Real MCTS SQL Server 2008 Exam 70-433 Prep Kit: Database Design By Valentine Boairkine, Mark Horninger, Herleson Pontes.