Get identity column value from sql server

In SQL Server, there are several functions that you can use to return the value created in an identity column. Here’s a brief explanation of each:

• @@IDENTITY: This function returns the last identity value generated in any table in the current session, regardless of the scope. This means it’s not limited to a specific scope.

• SCOPE_IDENTITY(): This function returns the last identity value inserted into an identity column in any table in the current session and the same scope. This means it only returns values inserted within the current scope.

• IDENT_CURRENT('table_name'): This function returns the last identity value generated for a specific table or view in any session. This means it’s not limited to a specific scope or session.

Here’s an example to illustrate the difference between these three functions:

CREATE TABLE t1(id int IDENTITY(1,1));
CREATE TABLE t2(id int IDENTITY(150,10));

CREATE TRIGGER t1_insert_trigger ON t1 FOR INSERT AS BEGIN
    INSERT t2 DEFAULT VALUES
END;

INSERT t1 DEFAULT VALUES;

SELECT @@IDENTITY AS [@@IDENTITY],
       SCOPE_IDENTITY() AS [SCOPE_IDENTITY()],
       IDENT_CURRENT('t1') AS [IDENT_CURRENT('t1')],
       IDENT_CURRENT('t2') AS [IDENT_CURRENT('t2')];

In this example, the @@IDENTITY function returns the last-inserted identity value, regardless of scope. The SCOPE_IDENTITY() function returns the identity value from the first table, because that was the last inserted-identity value within the current scope. The IDENT_CURRENT() function simply returns the last identity value inserted into the specified table, regardless of scope or session.