SCOPE_IDENTITY()

hi

what is difference between thos two's

SCOPE_IDENTITY()

and

@.@.IDENTITY

thanx

@.@.identity returns the LAST used identity value. It's not neccessarily the Scope value.

This example should explain it.

Code Snippet

create table t1(i int identity(1,1), j int)
create table t2(i int identity(100,10), j int)
go

create trigger _tr on t1
for insert
as
if @.@.rowcount=0 return;
insert t2(j)
select j from inserted;
go

insert t1(j) values(1)

select *, @.@.identity [@.@.ident], scope_identity() [scope]
from t1
go

drop table t2,t1
go

|||

thanx for reply.

u mean @.@.identity will return last value which is inserted into table t2... where as SCOPE_IDENTITY() will return the value of same table in this case t1

|||

Yup! Scope_identity() was invented to _correct_ the flaw of @.@.identity.

Think about it. If you were to enter an order into the Orders table. Wouldn't you want to know the last OrderID? @.@.identity will give you the wrong OrderID if there is a trigger that happens to insert into another table that has an identity column. Scope_identity() will guarantee that you get the correct OrderID.

|||

yes u are rite

thanx a lot