Global and Scope Identity In SQL

Difference between global and scope identity in SQL.

Global Identity

 

The @@IDENTITY is a system function that returns the last IDENTITY value generated for any table with an identity column under the current session, regardless of the scope of the T-SQL statement that generated the value.

 

Scope Identity

 

SCOPE_IDENTITY returns the last IDENTITY value inserted into an IDENTITY column in the same scope. SCOPE_IDENTITY returns the last  

 

Example

 

Here is a code snippet to create a table and add some data to it. It uses SQL Identity column.

 

create table emp ( id int identity(1,1) , Name varchar(30))

 

insert into emp values ('Rahul')

insert into emp values ('Keshav') 

 

print(@@identity)

print(scope_identity())

 

Output

 

@@identity       -- 2 

Scope_identity()--2 

 

Here is a detailed tutorial: Identity in SQL Server