Differences Between SCOPE IDENTITY, IDENT CURRENT, and IDENTITY in SQL Server

Introduction

In this article, we will learn about the Differences Between SCOPE IDENTITY, IDENT CURRENT, and IDENTITY in SQL Server.

What is SCOPE_IDENTITY in SQL Server?

SCOPE_IDENTITY

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 scope.
3. A scope is a module, a Stored Procedure, a trigger, a function, or a 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 is IDENT_CURRENT in SQL Server?

IDENT_CURRENT

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

What is @@IDENTITY in SQL Server?

@@IDENTITY

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 numerous 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 the transaction is rolled back.

Differences Between SCOPE IDENTITY, IDENT CURRENT, and IDENTITY

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, a trigger, a function, or a batch.

Step 1. Create two tables as below.

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

Step 2. Create a Trigger on the table1 as below.

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

Step 3. Make a select statement of both tables.

SELECT * FROM Table1

SELECT * FROM Table2

Step 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 the trigger is another scope.
SELECT SCOPE_IDENTITY() 
-- It will NOT consider identity value changed by trigger as the trigger is another scope.

Step 5. Run the following SQL statements for ident_current.

SELECT IDENT_CURRENT('Table1')
SELECT IDENT_CURRENT('Table2')

Step 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')

Conclusion

This article taught us the Differences Between SCOPE IDENTITY, IDENT CURRENT, and IDENTITY in SQL Server.