4 ways to get identity IDs of inserted rows in SQL Server

In this post I’ll explain the 4 methods available to get the IDs of newly inserted rows when the table has an identity Id column.

@@IDENTITY

This variable contains the last identity value generated by the current connection, is not limited to the scope of the code being executed. In case the current connection didn’t insert any row with an identity the property will have a NULL value.

INSERT INTO TableA (...) VALUES (...)
SET @LASTID = @@IDENTITY

This code will give you unexpected result if there is a trigger, running for inserts in TableA, that is inserting row in other tables with an identity. In this case the @@IDENTITY variable will give you the ID inserted by the trigger, not the one inserted in the current scope.

2) SCOPE_IDENTITY()

This function will return the value of the last identity inserted in the current executing batch.

The following code

INSERT INTO TableA (...) VALUES (...)
SET @LASTID = SCOPE_IDENTITY()

will return the last value inserted in TableA by our insert, even if other connections, or trigger fired by the current connection are inserting values with IDs.

This will be the best method to use in most of the cases.

3) IDENT_CURRENT(‘table’)

This function returns the last identity value inserted in the specified table, regardless of the scope and connection.

SET @LASTID = IDENT_CURRENT('dbo.TableA')

This function is available in SQL Server 2005 and newer versions.

4) OUTPUT

The output clause can be used to get IDs when inserting multiple rows.

DECLARE @NewIds TABLE(ID INT, ...)

INSERT INTO TableA (...)
OUTPUT Inserted.ID, ... INTO @NewIds
SELECT ...

This obviously will insert into @NewIds the Ids generated by the current statement. Other connection or code executing in different scopes will not affect the result of the operation.

Like IDENT_CURRENT, also this function is available in SQL Server 2005 and newer versions.