How to get an Identity value with SQL Server 2005 (concurrent user)

http://weblogs.asp.net/esanchez/archive/2006/04/24/443757.aspx

It's usual (actually, I'd say recommended) to have an Identity int column as primary key. I.e., it's a common thing to define a table like this :

CREATE TABLE HumanResources.Employees (

  Id int IDENTITY ( 1 , 1 ) NOT NULL,

  -- Other columns

,

CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED ( Id ASC )

)

Now what is an interesting question is how to find out the Id of a newly inserted row; usually you write code like this:

DECLARE @NewId AS int

INSERT INTO HumanResource s.Employees

  ( /* column names */ )

  VALUES ( /* column values */ )

SELECT @NewId = @@Identity

In this way we get the newly generated identity in @NewId . First of all *this code sample is wrong* Why? Because @@Identity returns the last identity value inserted in *any table or session of the database* , and so in a high (or even medium) concurrency environment @NewId will give you unexpected values. For this reason, it's a far better idea to use such functions as Scope_Identity() or Ident_Current().

I nterestingly, SQL Server 2005 introduces the OUTPUT clause that allows us to find out the values (original or new) for columns manipulated by any INSERT, UPDATE or DELETE statement. With OUTPUT's help, the previous example can be rewritten like this:

DECLARE @InsertedRows AS TABLE ( Id int )

DECLARE @NewId AS INT

INSERT INTO HumanResources.Employees

  ( /* column names */ )

OUTPUT Inserted. Id INTO @InsertedRows

  VALUES ( /* column values */ )

SELECT @NewId = Id FROM @InsertedRows

Note the @InsertedRows temporary table declaration and the use of the OUTPUT clause right before the VALUES clause inside the INSERT statement. This code is not only concurrency-safe, but it allows us to get the values of other columns that could have been generated (e.g. by DEFAULTs or TRIGGERs). Moreover, as I already mentioned, it can also be used with UPDATEs and DELETEs which makes it useful for those who like to have record-level logs (even though I prefer to use business level logs, but that's another story...)