What SCOPE_IDENTITY is

SCOPE_IDENTITY is:

1. SCOPE_IDENTITY returns the last IDENTITY value inserted into an IDENTITY column in the same scope.
2. SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
3. A scope is a module; a Stored Procedure, trigger, function, or batch.
4. Thus, two statements are in the same scope if they are in the same Stored Procedure, function, or batch.
5. 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.

What IDENT_CURRENT is

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.

What @@IDENTITY is

@@IDENTITY is:

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.

Differences

The differences between them are:

1. SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.
2. SCOPE_IDENTITY and @@IDENTITY will return the last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope. A scope is a module; a Stored Procedure, trigger, function, or batch.

Example

1. Create two tables as below:

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

2. Create a trigger on table1 as below:

CREATE TRIGGER TG_Table1 ON Table1 FOR INSERT
AS
BEGIN
       INSERT table2 DEFAULT VALUES
END

3. Do a select statement of both the tables:

SELECT * FROM Table1

SELECT * FROM Table2

4. Run the following SQL statements and observe the output

INSERT Table1 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.

5. Run the following SQL statements for ident_current:

SELECT IDENT_CURRENT(‘Table1’)
SELECT IDENT_CURRENT(‘Table2’)

6. Run the following SQL statements in a different query window, in other words a different session:

SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()

SELECT IDENT_CURRENT(‘Table1’)
SELECT IDENT_CURRENT(‘Table2’)

Hope this will help you to clear your ideas.