IDENT_CURRENT vs SCOPE_IDENTITY vs @@IDENTITY vs. IDENTITY in SQL Server

These are the most common functions to work with identity values in SQL Server. This blog looks at IDENT_CURRENT vs. SCOPE_IDENTITY vs. @@IDENTITY vs. IDENTITY comparison.

Let’s look at the definition first.

IDENT_CURRENT: It returns the last identity created for a particular table or view in any session. It is used as

SELECT IDENT_CURRENT('tablename') AS 'IDENTCURRENT'

Care should be taken when using this as it returns the value from any session not from the session where it is called from.

SCOPE_IDENTITY: It returns the last identity from a same session and the same scope. A scope is a stored procedure/trigger etc. It is called as

SELECT SCOPE_IDENTITY() AS 'SCOPEIDENTITY'

@@IDENTITY: It returns the last identity from the same session. It is used as

SELECT @@IDENTITY AS '@@IDENTITY'

IDENTITY: This is used to insert an identity value in a table when using SELECT … INTO query. It is used as

SELECT IDENTITY(int,1,1) As Sno,'A' AS col1
into #tmp
from sys.objects

Let’s now look at the demo. The below query creates a table and a stored procedure which inserts a record into the table and returns the identity values using the above function from the procedure scope and from outside the procedure scope.

create table tblidentity(Sno int identity,col1 char(1))
GO
insert into tblidentity values('A'),('B')
GO
drop proc usp_identities
GO
Create procedure usp_identities 
AS
BEGIN

insert into tblidentity values ('C')
SELECT @@IDENTITY AS '@@IDENTITY_INPROC'
SELECT SCOPE_IDENTITY() AS 'SCOPEIDENTITY_INPROC'
SELECT IDENT_CURRENT('tblidentity') AS 'IDENTCURRENT_INPROC'END

Let’s now execute the procedure and analyze the values.

As shown above, all values from procedure and session scope return 3 except SCOPE_IDENTITY, which is 3 in procedure scope however it’s 2 in session scope. This is because of the insert statement executed earlier when preparing the table tblidentity.

A situation where @@IDENTITY and SCOPE_IDENTITY differ is when there is a trigger on a table which does an insert into a different table. In that case the @@IDENTITY returns the value from the trigger and not from the session unlike SCOPE_IDENTITY. An example of same is given below.

An After INSERT trigger on the table tblidentity inserts a value into tblident table. The @@IDENTITY returns the identity value from tblident table whereas the SCOPE_IDENTITY function returns the value from tblidentity table and from the current session and scope.

Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook