Auto Increment Functions

we have mainly 4 ways for get last identity (auto increment) values after an INSERT, SELECT INTO, or bulk copy operation.

Like  @@identity, scope_identity, ident_current and identity but there are a few differences between these functions like

@@IDENTITY:

returns the last IDENTITY value generated for any table under the current session, if multiple rows inserted then @@IDENTITY will return the last identity value generated.

If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL.

If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated.

If in current session identity value effected by trigger, function or a stored procedure then it will consider last one effected value.

Means will return last effected id may be temp/ physical table use in programmatically objects(like trigger, function or a stored procedure) and programmatically objects insert data in any types of table where table have auto increment column. so @@IDENTITY will return last inserted value (from any were in current session and execution).

SCOPE_IDENTITY():

returns the last IDENTITY(auto increment) value generated for any table under the current session and current scope. If in current session identity value effected by trigger, function or a stored procedure then it will not consider because by trigger, function or a stored procedure scope is outside to current scope.

IDENT_CURRENT():

when we need to get last identity(auto increment) values of particular table under any connection.

CREATE TABLE tbl_master(ID int identity,name varchar(100));
CREATE TABLE tbl_master_hist(ID int identity(100,1),name varchar(100),dt datetime);
go
create trigger t_insert_tbl_master
on tbl_master 
for insert as  
begin 
   insert tbl_master_hist(name,dt) 
   select name,getdate() from inserted;
end;
GO
INSERT into tbl_master values (‘abc’)
select @@identity,SCOPE_IDENTITY(),IDENT_CURRENT(‘tbl_master’)

IDENTITY :

when creating a new table and inserted data into other table  from existing table and it’s not have identity column then we can create as a identity like