@@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT

If you are doing multiple inserts and referencing key values in other inserts, you need to use correct id. Triggers can create a problem when you are inserting new data. You can use @@IDENTITY , SCOPE_IDENTITY() , or IDENT_CURRENT, but i prefer SCOPE_IDENTITY() after insert statement to get correct id.

I copied a script below to show you the example insert operation with insert trigger active. Insert trigger will simply copy inserted rows into other table. We start with different identity number for our simple test.

You will see that  SCOPE_IDENTITY()  gives correct id. It will return last identity value at current connection and at same scope. It does not differentiate table,so you should put that after your insert statement.

@@IDENTITY :  It will include statements from other scopes such as triggers, but in same session. It will report ids from trigger result in our example.

IDENT_CURRENT: it is limited to table you specified, but not to scope or session. It will report whatever  the latest value on that table.

BEGIN TRAN

create table dbo.Mydata
(id int not null identity(1,1) primary key,
col1 nvarchar(50) not null
)
go



create table dbo.MydataDummy
(id int not null identity(1,1) primary key,
col1copy nvarchar(50) not null,
)
go

--let say you have trigger at this mydata table writing to mydatadummy after each insert operation
CREATE TRIGGER dbo.IDummyData
   ON  dbo.Mydata
   AFTER INSERT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    insert into MydataDummy(col1copy)
    select inserted.col1 from inserted

END
GO
--we will insert 1 record to mydata 1 and 2records to mydatadummy to have different ids and then test the scope identity

insert into Mydata(col1)
select 'fdf'

--add one extra to dummy table
insert into MydataDummy(col1copy)
select 'extrafdf'

select 'init', * from Mydata
select 'init', * from MydataDummy


--===========Now we will do scope_identity to get id after insert

insert into Mydata(col1)
select 'item2atoriginaltable'
select 'item2atoriginaltable SCOPE_IDENTITY() ', SCOPE_IDENTITY()
select 'item2atoriginaltable @@identity', @@identity
select 'IDENT_CURRENT(''Mydata'')', IDENT_CURRENT('Mydata'),'IDENT_CURRENT(''MydataDummy'')', IDENT_CURRENT('MydataDummy')

--ok now we will do the same thing with @@identity, but this time we expect 3 at original table and 4 from other table
insert into Mydata(col1)
select 'item3atoriginaltable'

select 'item3atoriginaltable SCOPE_IDENTITY() ', SCOPE_IDENTITY()
select 'item3atoriginaltable @@identity', @@identity
select 'IDENT_CURRENT(''Mydata'')', IDENT_CURRENT('Mydata'),'IDENT_CURRENT(''MydataDummy'')', IDENT_CURRENT('MydataDummy')

ROLLBACK