Welcome to deBUG.to Community where you can ask questions and receive answers from Microsoft MVPs and other experts in our community.
0 like 0 dislike
1.1k views
in SQL Queries by 17 20 26

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?


1 Answer

0 like 0 dislike
by 16 25 39
selected by
 
Best answer

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

SCOPE_IDENTITY() vs @@IDENTITY in SQL

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

If you don’t ask, the answer is always NO!
...