Difference between @@IDENTITY, SCOPE_IDENTITY () and IDENT_CURRENT in SQL Server

	Session	Scope
@@IDENTITY	Same Session	Global scope value
SCOPE_IDENTITY()	Same Session	Local scope value
IDENT_CURRENT()	May be different	Depends on table name passed in parameter

Description:

In this example we explain that how to get last inserted ID of SQL table in SQL Server.

Or how to get last inserted record id in SQL Query.

In this example we explain that difference between @@IDENTITY, SCOPE_IDENTITY () and IDENT_CURRENT.

We used @@IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT properties to get the last inserted id or identity record in sql server table.

Below we explain that what the difference between scope_identity,@@identity and indent_current is.

@@IDENTITY

It is used to fetch/get last inserted record id of the any SQL Server table in current session.

SCOPE_IDENTITY ()

It is also used for get last inserted id of the table but it is limited to the current scope that means it will return id in current connection only.

IDENT_CURRENT

This property is used to return last inserted record id of the table. There is no any limit of the connection or session.

Syntax:-

SELECT SCOPE_IDENTITY()
SELECT IDENT_CURRENT(table_name)
SELECT @@IDENTITY

Example:

Insert INTO TABLE_ID (Table_NAME), Values (Table_Products)
DECLARE @ID int;
set @ID = SCOPE_IDENTITY(); 

Insert INTO Table_Products (ID, Product_Name) 
Values (@ID, SomeProduct)