Logo
Back

How to get the identity of an inserted row?

When inserting rows into a table that has an identity column, you often need to retrieve the newly generated primary key immediately for subsequent operations. Below are several methods in SQL Server to fetch the identity value right after an INSERT.

1. Using SCOPE_IDENTITY() (Recommended)

INSERT INTO Employees (FirstName, LastName)
VALUES ('John', 'Doe');

SELECT SCOPE_IDENTITY() AS NewEmployeeID;
  • SCOPE_IDENTITY() returns the identity value generated in the same scope and the same session.
  • It is the safest and most recommended choice because it avoids concurrency issues and triggers from other tables or sessions.

2. Using @@IDENTITY

INSERT INTO Employees (FirstName, LastName)
VALUES ('Jane', 'Smith');

SELECT @@IDENTITY AS NewEmployeeID;
  • @@IDENTITY returns the last identity value generated in the current session, regardless of the scope.
  • Potentially risky if your table has triggers that insert into other tables with their own identity fields, which could produce unexpected results.

3. Using IDENT_CURRENT('TableName')

SELECT IDENT_CURRENT('Employees') AS LastIdentity;
  • IDENT_CURRENT returns the last identity value generated for a specific table across all sessions, not just the current session.
  • Avoid using it if multiple concurrent sessions might be inserting into the same table simultaneously.

4. Using an Output Clause

DECLARE @InsertedIDs TABLE (NewID INT);

INSERT INTO Employees (FirstName, LastName)
OUTPUT inserted.EmployeeID INTO @InsertedIDs
VALUES ('Mark', 'Jones');

SELECT * FROM @InsertedIDs;  -- Contains the new identity values
  • The OUTPUT clause helps track multiple inserted rows at once.
  • Useful in batch inserts where several new records are created in a single statement.

Key Takeaways

  1. SCOPE_IDENTITY() is generally the most reliable method to retrieve the identity of just-inserted rows in the same scope.
  2. Avoid @@IDENTITY if you have triggers on the same table or if you operate in a multi-user environment.
  3. IDENT_CURRENT can be helpful in specific scenarios but isn’t scoped to your session, posing potential concurrency risks.
  4. The OUTPUT clause is great for bulk inserts.

Strengthen Your Database Skills with DesignGurus.io

  1. Grokking SQL for Tech Interviews – Learn essential SQL concepts, discover common interview questions, and master query optimizations.
  2. Grokking Database Fundamentals for Tech Interviews – Get a solid understanding of relational schemas, normalization, and indexing strategies.
  3. For more in-depth database modeling knowledge, explore Relational Database Design and Modeling for Software Engineers.

You can also browse the DesignGurus.io YouTube channel for free videos on system design and database topics. If you’re preparing for a high-stakes technical interview, consider their Mock Interviews service, where ex-FAANG engineers provide tailored feedback and real-world insights.

Conclusion

Retrieving the newly generated primary key immediately after an insert is crucial in many database operations. SCOPE_IDENTITY() is your best bet for avoiding concurrency pitfalls, while the OUTPUT clause shines for batch inserts. By mastering these techniques, you can simplify complex workflows and ensure data consistency in your SQL Server environment.

CONTRIBUTOR
TechGrind