MSSQLTips

http://www.mssqltips.com/tip.asp?tip=1385

The really great news is that there is a new way to get the Identity value returned if you are using SQL Server 2005 . . . check out the OUT variables that you can use during an INSERT statement.  You can return the ID column or anything else (even the whole bloody record ;-).

While you could use OUTPUT clauses, I prefer the simplicity of SCOPE_IDENTITY(); I don't have to build a structure to capture the id value and then SELECT it out. :)

OUTPUT clauses are great for sending data to a secondary table. In fact, I just used it in an archiving stored procedure where I DELETE from the current table and OUTPUT directly to the history table. 

However, the use of SCOPE_IDENTITY() requires an addition SQL statement execution.  While this may not seem like a lot of time, it can add up if you are doing a few million insertions.

To me, the use of the OUTPUT clause seems much simpler than setting up and executing an additional SQL statement.  It also precludes any confusion over exactly which Identity retrieval method to use.

 However, that's just me and you are certainly entitled to use whichever approach suits you best (and works best for ou).

The value for SCOPE_IDENTITY() is stored in memory (as is @@IDENTITY for that matter).  The SQL statement is grabbing the value right from memory.

In the simple script below, displaying the execution plan shows that 70% of the batch is working with the OUTPUT approach to capturing an identity while the remaining 30% is attributed to the scope_identity() approach.  Each proc inserts 1 row.

set nocount on

if object_id('test') is not null
   drop table test
go
create table test (id int identity)
go

if object_id('test1') is not null
   drop proc test1; 
if object_id('test2') is not null
   drop proc test2;
go

create proc test1
as
set nocount on

  declare @temp table (id int)
  declare @id int

  insert into test output inserted.id into @temp(id) default values
  select @id = id from @temp
  print cast(@id as varchar)

go

create proc test2
as
  set nocount on

  insert into test default values
  print cast (scope_identity() as varchar)

go

truncate table test
go
exec test1
go
truncate table test
go
exec test2
go