Using OUTPUT Clause to return inserted identity value (or any inserted value) in SQL Server

I recently came across a bug in SQL Server 2008 that can cause the SCOPE_IDENTITY() Function to return an incorrect value for the recently inserted row if the query has a parallel execution plan. While SCOPE_IDENTITY() is generally a pretty safe way to go, it is not fool-proof. The surefire way to get the correct value is to use an output clause to return the value needed instead. Here is an example of using the OUTPUT clause to get the (in this case auto-incremented) identity value of the recently inserted row:

DECLARE
    @UserID INT = 1,
    @UserTier INT = 1,
    @Comment VARCHAR(1000) = 'I am inserting these values and returning the auto-incrementing ID using the OUTPUT clause'

INSERT INTO SampleDB.dbo.TblName
(
    UserID,
    UserTier,
    Timestamp,
    Comment
)
OUTPUT              /* <== This clause is what returns inserted value */
    INSERTED.ID
VALUES
(
    @UserID,
    @UserTier,
    GETDATE(),
    @Comment
)

This should be the standard going forward and is much better than Selecting SCOPE_IDENTITY() or @@IDENTITY at the end of the query to get your recently inserted row’s identity column.

It should also be noted that you can return more than just the identity column, you can return as many inserted columns as you want to and it will return the values:

DECLARE
    @UserID INT = 1,
    @UserTier INT = 1,
    @Comment VARCHAR(1000) = 'I am inserting these values and returning the auto-incrementing ID using the OUTPUT clause'

INSERT INTO SampleDB.dbo.TblName
(
    UserID,
    UserTier,
    Timestamp,
    Comment
)
OUTPUT              /* <== This clause is that returns inserted values */
    INSERTED.ID,
    INSERTED.Timestamp
VALUES
(
    @UserID,
    @UserTier,
    GETDATE(),
    @Comment
)

This will return the identity and the timestamp that were just inserted: