SCOPE_IDENTITY() vs @@IDENTITY in SQL

Question

I am starting learning SQL server, and I don't understand the difference between SCOPE_IDENTITY(), @@IDENTITY

On my side, I have created a table and insert data using the below T-SQL

Create Table Test1 (ID int IDENTITY(1,1) ,Value nvarchar(20))

insert into Test1 values ('bb')

Then I run the below query

select SCOPE_IDENTITY()
select @@IDENTITY

I got the same result 1 and 1.

Could you please explain What's the difference between SCOPE_IDENTITY(), @@IDENTITY in SQL?

Answer 1

SCOPE_IDENTITY() vs @@IDENTITY in SQL

SCOPE_IDENTITY,@@IDENTITY, IDENT_CURRENT are doing the same function that is returning the value that is inserted in the identity column.

IDENT_CURRENT:

Returns the last generated identity across any session and any scope.

SCOPE_IDENTITY :

Returns the last generated identity within The same session and same scope.

@@IDENTITY

Returns the last Generated identity within the same session.

To see the difference between them you can create two tables and create Trigger on any table so when performing an insert in the first table the trigger is executed and insert a row in another table, then you can Execute this query :

select SCOPE_IDENTITY()
select @@IDENTITY

you will See different result the result 6 refer to the last identity in Test1 Table(same scope and session) and the result 7 refer to to the last identity in Test2 Table (same session)

See also SCOPE IDENTITY