In SQL Server, the SCOPE_IDENTITY() function is used to retrieve the last identity value that was inserted into an identity column in the same scope. This means that it returns the identity value for the last row that was inserted in the current session and within the current execution context. Here's how you can use it:
Basic Usage
1. Insert a New Record: You first insert a record into a table that has an identity column.
2. Call SCOPE_IDENTITY(): Immediately after the insert statement, you call SCOPE_IDENTITY() to get the identity value generated for that insert.
Example
Let's say you have a table called Employees:
CREATE TABLE Employees (
EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50)
);
You can use SCOPE_IDENTITY() as follows:
-- Insert a new employee
INSERT INTO Employees (FirstName, LastName)
VALUES ('John', 'Doe');
-- Retrieve the last inserted identity value
DECLARE @LastInsertedID INT;
SET @LastInsertedID = SCOPE_IDENTITY();
-- Output the last inserted identity value
SELECT @LastInsertedID AS LastInsertedEmployeeID;
Important Notes
1. Scope: SCOPE_IDENTITY() only returns the identity value for the last operation that inserted a row in the same scope (i.e., the same batch or stored procedure).
2. Session: It's limited to the current session. Multiple sessions can have different identity values.
3. Alternative Functions: Be aware that there are other functions like @@IDENTITY and IDENT_CURRENT('table_name'), but they are different:
- @@IDENTITY retrieves the last identity value generated for the current session, but it is not limited to the current scope, meaning it can return values from triggers.
- IDENT_CURRENT('table_name') returns the last identity value generated for a specific table in any session and any scope.
Using SCOPE_IDENTITY() is the preferred method when you want to safely retrieve the last identity value generated in your inserts.