From the BlogSubscribe Now

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:

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:

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

output clause values

So you can then retrieve and use those values how you normally would if you were using a select statement.

Speak Your Mind


seven + 9 =