What is SCOPE_IDENTITY() and Why we use SCOPE_IDENTITY() in SQL Server ?

SCOPE_IDENTITY() SQL Server me ek function hai jo aakhri IDENTITY value ko return karta hai jo current session aur scope ke andar insert hui ho. Ye zyada tar use hota hai tab jab ek table me naye record insert karte hain jisme IDENTITY column ho, aur hame us naye record ki ID chahiye ho.

Example: SCOPE_IDENTITY() with INSERT Statement

Maan lijiye ek table Employees hai:

CREATE TABLE Employees (

    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,

    Name NVARCHAR(100),

    Designation NVARCHAR(100)

);

Ab hum is table me ek record insert karenge aur uski generated EmployeeID value ko SCOPE_IDENTITY() se retrieve karenge.

SQL Example:

INSERT INTO Employees (Name, Designation)

VALUES ('John Doe', 'Software Engineer');

-- Get the last inserted identity value

SELECT SCOPE_IDENTITY() AS LastInsertedID;

Yahaan:

• INSERT INTO Employees ek new employee ka record insert karega.

• SCOPE_IDENTITY() function usi query ka EmployeeID return karega jo abhi-abhi insert hua.

Result:

Agar EmployeeID ki value 5 thi to output hoga:

LastInsertedID

--------------

5

---

Example: Using SCOPE_IDENTITY() in Stored Procedure

Agar aap stored procedure ka use karte hain:

CREATE PROCEDURE AddEmployee

    @Name NVARCHAR(100),

    @Designation NVARCHAR(100),

    @NewEmployeeID INT OUTPUT

AS

BEGIN

    INSERT INTO Employees (Name, Designation)

    VALUES (@Name, @Designation);

    -- Capture the new identity value

    SET @NewEmployeeID = SCOPE_IDENTITY();

END;

Call the Procedure:

DECLARE @ID INT;

EXEC AddEmployee 'Jane Smith', 'Project Manager', @ID OUTPUT;

PRINT 'New Employee ID: ' + CAST(@ID AS NVARCHAR);

Output:

New Employee ID: 6

---

Why Use SCOPE_IDENTITY()?

1. Isolated to Current Scope: Ye sirf wahi IDENTITY value deta hai jo current session aur scope ke andar create hui ho.

2. Safer Than @@IDENTITY: @@IDENTITY kisi bhi trigger ya session ke andar generate hui ID de sakta hai, jo galat ho sakta hai.

Aapke kisi bhi project me relational references maintain karne ke liye SCOPE_IDENTITY() kaafi useful hai.