Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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;

Comments

Posted by dgrotto on 2 January 2012

Glenn-

I was researching the use of SCOPE_IDENTITY and @@IDENTITY when I ran across this blog post. Thanks for the link to the MS KB.

Question for your solution: what happens when the INSERT is rolled back? Won't your table variable contain erroneous info?

Leave a Comment

Please register or log in to leave a comment.