Identity and scope Identity

Scope_Identity()
This function returns Last generated Id in the same scope. A scope may be store Procedure,Trigger,Function or Batch.

IDENT_CURRENT('table_name')
Returns the last identity value generated for a specified table or view. The last identity value generated can be for any scope.

@@IDENTITY
@@IDENTITY is a global variable. And returns the last identity value generated for any table across all scopes.

We have two tables Employee1 and Employee2

Employee1 Structure
Id int Not NULL IDENTITY(1,1),
Name varchar(100) NULL,
Salary int


Employee2 Structure
Id int Not NULL IDENTITY(1,1),
Name varchar(100) NULL

You can create the duplicate copy of Employee1 as

Select * into Employee2 from Employee1

And a procedure is used to insert values in first table.

Crate Procedure sp_InsertEmp(@Name varchar(50),@Salary int)
as
Begin
insert into Employee(Name,Salary) values(@Name,@Salary)
Select @@Identity
End

And a trigger is used to insert values in second table.

Create Trigger InsertInEmplyee2 On Employee1 For Insert
As
Begin
Declare @Name varchar(100),
Select @Name = [Name] from Inserted
Insert into Employee2(Name) values (@Name).
End

Here you can not use Identity it will give you last generated Id that will be the Id of Employee2. Bu here we want last generated Id of Employee1. So in this scenario we can use Scope_Identity() or Ident_Current(‘Employee1’). If you are using two tables in the same procedure and you want id generated by a specific table than better is use Ident_Current(‘Table_Name’) instead of Scope_Identity().

When you create an Identity column there are two parameters First is seed and second one is Increment.

Ex. Id int IDENTITY(1,2).. It will start from 1 and per row 2 will be increment number.

You can download the sample sp and trigger :- http://www.box.net/shared/ydez5c49jx