Friday, February 25, 2011

Identity and scope Identity

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

Returns the last identity value generated for a specified table or view. The last identity value generated can be for any scope.

@@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)
insert into Employee(Name,Salary) values(@Name,@Salary)
Select @@Identity

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

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

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 :-

No comments: