Using the OUTPUT clause to retrieve new identity values from SQL Server 2008

Microsoft has a new KB article outlining issues with using SCOPE_IDENTITY and @@IDENTITY to retrieve values inserted into an identity column in a table. This particular issue shows up when you have parallel execution plans, but I have also seen DML triggers cause problems when you use @@IDENTITY.  The article lists several workarounds, including setting MAXDOP=1 at the query or instance level. In my opinion, the best option is to use the output clause of the INSERT table to retrieve the identity value.

Here is an example of how to use the OUTPUT clause:

-- Example of using OUTPUT clause in an INSERT statement
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry
USE AdventureWorks;
GO

-- Declare table variable to hold results
DECLARE @MyTableVar table( ScrapReasonID smallint,
                           Name varchar(50),
                           ModifiedDate datetime);

-- Insert into table with IDENTITY column
-- Use OUTPUT clause to retrieve new value                           
INSERT Production.ScrapReason
OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

--Display the result set of the table variable.
SELECT ScrapReasonID, Name, ModifiedDate 
FROM @MyTableVar;

This entry was posted in SQL Server 2008. Bookmark the permalink.

4 Responses to Using the OUTPUT clause to retrieve new identity values from SQL Server 2008

  1. Robert says:

    I love the output clause. We have several stored procedures where it enables follow-on processing just on the data which was modified in a previous query. Even better, the "previous" query does not even need to be adjacent.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s