@@IDENTITY , SCOPE_IDENTITY():

SELECT @@IDENTITY: returns the last identity value generated for any table in the current session, across all scopes(i.e. global scope).

1. @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
2. After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement.
3. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL.
4. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated.
5. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.

Note:

It will return last or newly inserted record id of any table in current session but it’s not limited to current scope. In current session if any trigger or functions inserted record in any table that it will return that latest inserted record id regardless of table. We need to use this property whenever we don’t have any other functions or triggers that run automatically.

SELECT IDENT_CURRENT : returns the last identity value generated for any table in the current session and the current scope(i.e. local scope).


SELECT SCOPE_IDENTITY(): returns the last identity value generated for a specific table in any session and any scope(i.e. global scope).


Example

Now i am going to create a two new tables

Creation of two tables

CREATE TABLE Table1(id int IDENTITY)


CREATE TABLE Table2(id int IDENTITY(100,1))

Create a trigger on table1 as below:

CREATE TRIGGER TG_Table1 ON Table1 FOR INSERT

AS

BEGIN       

        INSERT table2 DEFAULT VALUES

END

 Run the following SQL statements and observe the output

INSERT empTable1 DEFAULT VALUES

SELECT @@IDENTITY      -- It will consider identity value changed by trigger as trigger is another scope.


SELECT SCOPE_IDENTITY() -- It will NOT consider identity value changed by trigger as trigger is another scope.

Scope_Identity()

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

A scope is a module; a Stored Procedure, trigger, function, or batch.

The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.


Note:

This property will return last or newly inserted record id of table in current session or connection and it’s limited to current scope that means it will return id of newly inserted record in current session / connection stored procedure or query executed by you in current scope even we have any other functions or triggers that run automatically. Its better we can go with property whenever we need to get last or newly inserted record id in table.

IDENT_CURRENT is:

1. IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
2. IDENT_CURRENT is not limited by scope and session; it is limited to a specified table.

SELECT IDENT_CURRENT('empTable2')