What Is The Differences Between SQL Server SCOPE IDENTITY, IDENT CURRENT, and IDENTITY

We will learn about the differences between SCOPE IDENTITY, IDENT CURRENT, and IDENTITY in SQL Server in this essay.

What is SQL Server’s SCOPE_IDENTITY?

• The last IDENTITY number inserted into an IDENTITY column in the same scope is returned by SCOPE_IDENTITY.
• SCOPE_IDENTITY returns the most recently generated identity number for any table in the current session and scope.
• A module, a Stored Procedure, a trigger, a function, or a group are all examples of scopes. Thus, two lines in the same Stored Procedure, function, or batch are in the same scope.
• If the SCOPE_IDENTITY() function is called before any insert statements into an identity column appear in the scope, it will return NULL.

What exactly is IDENT_CURRENT in SQL Server?

• IDENT_CURRENT returns the most recently generated identity value for a particular table in any session and scope.
• IDENT_CURRENT is not scope or session restricted, but rather to a specific table.

What is SQL Server’s @@IDENTITY?

• @@IDENTITY returns the most recently generated identity number for any table in the current session across all scopes.
• Following the completion of an INSERT, SELECT INTO, or bulk copy statement, @@IDENTITY holds the last identity number generated by the statement.
• @@IDENTITY returns NULL if the statement did not affect any tables with identity fields. If multiple rows are inserted, producing multiple identity values, @@IDENTITY returns the most recently generated identity value.
• If the INSERT or SELECT INTO statement or bulk copy fails or the transaction is rolled back, the @@IDENTITY number does not revert to its prior value.

The distinctions between SCOPE IDENTITY, IDENTITY CURRENT, and IDENTITY

• SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are methods that return values that have been put into IDENTITY columns.
• SCOPE_IDENTITY and @@IDENTITY will yield the most recently produced identity values in any database in the current session. SCOPE_IDENTITY, on the other hand, gives numbers inserted only within the present scope; @@IDENTITY is not scope-specific. A module, a Stored Procedure, a trigger, a function, or a group are all examples of scopes.

Step 1. Create two tables as below.

CREATE TABLE Car(id int IDENTITY)
CREATE TABLE Animal(id int IDENTITY(100,1))

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

CREATE TRIGGER TG_Car ON Car FOR INSERT
AS
BEGIN
       INSERT animal DEFAULT VALUES
END

Step 3. Make a select statement of both tables.

SELECT * FROM Car

SELECT * FROM Animal

Step 4. Run the SQL queries below and examine the results.

INSERT Car DEFAULT VALUES
SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()

Step 5. Execute the SQL queries for ident_current listed below.

SELECT IDENT_CURRENT('Car')
SELECT IDENT_CURRENT('Animal')

Step 6. Run the following SQL queries in a separate query window, or in another session.

SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()

SELECT IDENT_CURRENT('Car')
SELECT IDENT_CURRENT('Animal')