How to get the identity of an inserted row?

Round up multiple culprits with OUTPUT

Whenever the task involves capturing the identity values for multiple rows or preparing for potential error rollbacks, slide in the OUTPUT clause. It serves as a trusty aid, swiftly presenting a results set from an insert, even amidst a potential transaction rollback storm.

Tackle parallel executions like a boss

In the world of high concurrency and parallel inserts, the OUTPUT clause provides much-needed sanity by ensuring that each newly inserted row can be correctly linked with its corresponding identity value.

Dodge triggers' fire with OUTPUT

Beware of triggers as they may spring surprise additional rows into your table, generating new identity values that could confuse the humble @@IDENTITY. The OUTPUT clause smirks at this challenge and works its magic before any triggers get a chance to fire.

Implement OUTPUT clause like a champ

Deploy your captured identities in an @IdentityOutput table variable, using a badass INSERT ... OUTPUT construct:

DECLARE @IdentityOutput TABLE (ID int); -- Prepare your catch net.

INSERT INTO Table (Column)
OUTPUT INSERTED.ID INTO @IdentityOutput  -- Throw the net right here!
VALUES ('Data');

SELECT ID FROM @IdentityOutput;  -- Voila! Gotcha.


Scope it out: Know where your identities dwell

Unraveling the identity enigma entails understanding scope with finesse.

Caution: @@IDENTITY and its love for scopes

@@IDENTITY might seem like the perfect ally when fetching the last Session's identity, but beware! Intricate precision and sharp accuracy are not its strong suit!

Table-specific Reality with IDENT_CURRENT

For those rare yet specific moments, drop in IDENT_CURRENT('tableName') to fetch the most recent identity spawned for any session or scope from a certain table.

Align your types

Ensure your data types are in sync to avoid unexpected conversions and potential data hiccups when stashing returned identities by the OUTPUT clause in a table variable.

Taking SCOPE_IDENTITY() to different scenarios

After insert - The classic path

The common and favored scenario involves using SCOPE_IDENTITY() swiftly after an INSERT statement to reel in the new identity:

INSERT INTO Table(Name) VALUES ('New Entry');  -- New kid on the block
SELECT SCOPE_IDENTITY() AS NewId;              -- Catch 'em ID!


Inside a transaction - Wearing a safety net

An often overlooked, SCOPE_IDENTITY() transforms into a lifesaver in transaction scenarios, keeping your operation anchored:

BEGIN TRANSACTION
INSERT INTO Table(Name) VALUES ('New Entry');   -- And this one is going into the wild.
SELECT SCOPE_IDENTITY() AS NewId;               -- Got 'em!
COMMIT TRANSACTION -- Seal the deal!


In complex operations

During intricate batch operations involving multiple inserts, SCOPE_IDENTITY() outmaneuvers @@IDENTITY, ensuring you get the "right" ids for your batch.