Obtaining the last generated IDENTITY value

There are many ways in SQL Server to obtain last inserted identity value. Two of them is @@IDENTITY and SCOPE_IDENTITY
functions. @@IDENTITY function returns the last generated identity for the current connection; SCOPE_IDENTITY function
returns the last generated identity value within the current scope.

@@IDENTITY function usually returns the identity of the previously inserted row. However if you are inserting into a
table that has defined triggers and triggers performs insert in another table that includes identity column than @@IDENTITY
will return the identity value of that other table.

SCOPE_IDENTITY resolves the above problem as it returns the last identity value generated within the current scope only.
The current scope can be the executing stored procedure, trigger or batch.

Exmaple to show difference between abovee discussed function.

CREATE TABLE Table1
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    col1 VARCHAR(50),
    col2 MONEY
)
 
CREATE TABLE Audit
(
    ID BIGINT IDENTITY(1000000,1) PRIMARY KEY,
    Col1 VARCHAR(50),
    Col2 MONEY
)

CREATE TRIGGER CopyTable1ToAudit ON Table1 AFTER INSERT AS
INSERT INTO Audit (Col1, Col2)
SELECT Col1, Col2 FROM inserted

INSERT INTO Table1 VALUES ('abc', 100)
SELECT @@IDENTITY       -- 1000000
SELECT SCOPE_IDENTITY() -- 1