SQL 2005+

Obtaining the Last Inserted Identity in SQL Server

by Richard Carr, published at http://www.blackwasp.co.uk/SQLScopeIdentity.aspx

SQL Server identity columns allow a sequence of numbers to be generated so that a unique number can be applied for each new row in a table. As these values are produced automatically, it is sometimes necessary to retrieve an identity after creation.

@@IDENTITY and SCOPE_IDENTITY

Identity columns are often used to generate unique values for the primary key of a table. They are useful because they provide predictable, sequential numbers that can be short and efficient for small data sets and longer for large tables. When you insert a new row in a table with an identity column, the column value is generated automatically. Sometimes it is important to know the last number that was inserted. For example, in a sales order processing system you may be inserting an order header and some associated order lines, linked using a foreign key relationship. After inserting the header row you will need the identity value to add to the line rows.

There are several ways to obtain the last inserted identity value. A common method is to run the @@IDENTITY system function. This function returns the last generated identity for the current connection. In many cases this gives the identity of the previously inserted row. However, the function can be problematic if the table that you are inserting into has defined triggers. If one of these triggers performs an insert on another table that also includes an identity column, it will be the identity of the other table that is returned by @@IDENTITY.

A second function is available that resolves the above problem. SCOPE_IDENTITY is a function that returns the last generated identity value within the current scope. The current scope is the extent of the executing stored procedure, trigger, function or batch. This means that if a row is created within a batch or procedure, the identity of that row will be returned, even if other rows are generated by triggers. In most cases it is safer to use SCOPE_IDENTITY to avoid problems if triggers are added to a table in the future. The use of @@IDENTITY may work initially but fail unexpectedly if a trigger is created.

We can demonstrate the difference between the two functions using two tables and a trigger. To follow the demonstration, create a new database and execute the following script. This creates two tables. The first will be used to temporarily record sales and will be cleared periodically. The sales are given a unique value by the identity column. The second, "Audit" table will hold a copy of the sales rows forever and has an identity column that starts at a higher value.

CREATE TABLE Sales
(
    SalesID INT IDENTITY(1,1) PRIMARY KEY,
    Item VARCHAR(50),
    Price MONEY
)

CREATE TABLE Audit
(
    AuditID BIGINT IDENTITY(1000000,1) PRIMARY KEY,
    Item VARCHAR(50),
    Price MONEY
)

To copy information from the Sales table to the Audit table, add the following trigger:

CREATE TRIGGER CopySalesToAudit ON Sales AFTER INSERT AS
INSERT INTO Audit (Item, Price)
SELECT Item, Price FROM inserted

With the simple database schema complete, we can insert a row into the Sales table and read the values of the @@IDENTITY and SCOPE_IDENTITY functions using the following script. Note that the @@IDENTITY function returns the identity value from the Audit table but SCOPE_IDENTITY returns the value from the inserted Sales row.

INSERT INTO Sales VALUES ('PC', 999.99)
SELECT @@IDENTITY       -- 1000000
SELECT SCOPE_IDENTITY() -- 1