difference between @@Identity And Scope Identity()

In this article I will explain the difference between @@Identity And Scope_Identity().

Many times we want the inserted identity value. So there comes two terms @@Identity And Scope_Identity().
So to understand these things you should clear these terms first scope and session.

@@Identiy will return identity value in current session but any scope.
Scope_Identity() will return identity value in current session in current scope.

Now what are these Scope and Session?

Session means the connection that is currently executing that command.
Scope I will Explain with this :
Suppose you have a stored procedure for inserting some values in a table. You are calling other stored procedure in that procedure means nested in that .So your outer Proc context is one scope and your nested procedure that you have called is in other scope. Like wise suppose you have written a query for inserting records into a table and you have written trigger on that which will insert record in another table, so in this case your query context is one scope and your trigger context is other scope. Now Consider these

Examples

To explain that I have taken two tables and one trigger.When record is inserted in one table its id value is inserted in second table through that trigger.

Create table Test_Identity
(id int identity ,[task] varchar(20) ,assigned_to varchar(20))
Go
insert into Test_Identity
select 'task1' , 'bsmith'
Union all
select 'task2' , 'csmith'
Go
Create table Test_Identity_Second_Table
(id int identity ,[Test_Identity_From_First_Table] int )
Go
Create trigger _Insert_Test_Trigger_Identity
on [Test_Identity]
After insert
As
Begin
insert into Test_Identity_Second_Table
([Test_Identity_From_First_Table])
Select id from inserted
End
GO
insert into Test_Identity
([task],[assigned_to])
values
('task3' , 'esmith')
GO
Select @@identity
–Output
—————————————
1
Select Scope_Identity()
—————————————
3
Go

In above you got 1 as  returned identity value in current session but any scope .So 1 is the value of latest identity which has been inserted in Test_Identity_Second_Table through trigger and you got 3 for Scope_Identity because At current scope 3 is the value of identity which has been inserted that is in table Test_Identity.

Now second example through stored procs

Make Two Procs. Drop that tables and again create them

Go
drop table Test_Identity
drop table Test_Identity_Second_Table
GO
Create table Test_Identity
(id int identity ,[task] varchar(20) ,assigned_to varchar(20))
Go
insert into Test_Identity
select 'task1' , 'bsmith'
Union all
select 'task2' , 'csmith'
Go
Create table Test_Identity_Second_Table
(id int identity ,[Test_Identity_From_First_Table] int )
Go
CReate proc Insert_Proc_Test_Identity_Second_Table
@id int
as
begin
insert into Test_Identity_Second_Table
([Test_Identity_From_First_Table])
values
(@id)
end
GO
Create proc Insert_Proc_Test_Identity
@task varchar(20),
@assigned_to varchar(20)
as
begin
declare @id as int
insert into Test_Identity
([task],[assigned_to])
values
(@task ,@assigned_to)
Set @id=Scope_Identity()
exec Insert_Proc_Test_Identity_Second_Table @id
Select Scope_Identity() as [ScopeIdentity]
Select @@identity as [Identity]
end
Go
–Test It
exec Insert_Proc_Test_Identity 'task3' , 'esmith'
--Output
ScopeIdentity
—————————————
3
Identity
—————————————
1

In Above you got 1 as returned identity value in current session but in any scope.So 1 is the value of  latest identity which has been inserted in Test_Identity_Second_Table through nested stored procedure Insert_Proc_Test_Identity_Second_Table and you got 3 for Scope_Identity because at current scope 3 is the value of identity which has been inserted,ie in table Test_Identity in current context of Proc Insert_Proc_Test_Identity.