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
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
You use SCOPE_IDENTITY like
Syntax of @@IDENTITY is
You use @@IDENTITY like
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.
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.