Difference between @@IDENTITY, SCOPE_IDENTITY () and IDENT_CURRENT

@@IDENTITY

Syntax : SELECT @@IDENTITY

The keyword "@@IDENTITY" in current session is capable of returning newly inserted or last record id of any given table but it’s functionality is not limited to current scope. If any trigger or functions in current session inserts a record in any table than "SELECT @@IDENTITY" will return this latest inserted record id regardless of table.

SCOPE_IDENTITY()

Syntax : SELECT SCOPE_IDENTITY()

The keyword "SCOPE_IDENTITY()" in current session is capable of returning newly inserted or last record id of any given table. The functionality of "SCOPE_IDENTITY()" is limited to current scope i.e id of only newly inserted record via stored procedure or query execution in current session / connection will be returned even if 
there are other functions or triggers that run automatically. 

IDENT_CURRENT

Syntax : SELECT IDENT_CURRENT(table_name)

The keyword "IDENT_CURRENT" is capable of returning newly inserted or last record id of specified table. It’s limitation is not to any session or scope but is limited to specified table.

Below is an example to demonstrate "@@IDENTITY","SCOPE_IDENTITY()","IDENT_CURRENT" property.

Create two tables named DEMO1 and DEMO2

CREATE TABLE DEMO1 (Id INT IDENTITY)
CREATE TABLE DEMO2 (Id INT IDENTITY(100,1))

Create a trigger in the same session as the session in which above table were created. This trigger will be executed when data is inserted into DEMO1 table.

CREATE TRIGGER TRIGINSERT ON DEMO1 FOR INSERT
AS
BEGIN
INSERT DEMO2 DEFAULT VALUES
END

Initially when following query are executed,both query will return empty value.

SELECT * FROM DEMO1
SELECT * FROM DEMO2

Now insert default values in DEMO1 table by running following query.

INSERT DEMO1 DEFAULT VALUES

After inserting values in DEMO1 table, values of @@identity, scope_identity() and ident_current(‘tablename’) can be checked. Execute following query in sql server.

1) SELECT @@IDENTITY 

The above query will return record id 100 as it was last value inserted by trigger i.e as soon as default values was inserted in DEMO1 table, trigger TRIGINSERT was fired which insert default values in DEMO2 table therefore in DEMO2 table last record id was inserted.  

2) SELECT SCOPE_IDENTITY()

This query will return record id 1 as that was the last record id inserted through the query "INSERT DEMO1 DEFAULT VALUES" in current session,it does not take into account the values inserted into DEMO2 by trigger fired when default values were inserted in DEMO1 table.

3) SELECT IDENT_CURRENT('DEMO2')

This query does not depend on current session,it only depends on specified table,thus it returns values of "DEMO2" table.

Executing all three queries together gives following output :-