When
we are working with identity columns the problem arise how to determining the
value of the identity (column with identity property) that was just created. The
new identity value is created with SQL Server at the time of the insert and we
want to display this inserted identity value to user or we want to display the
row on a user-interface grid within an application.
1 @@IDENTITY: This is global variable .It returns the last identity value
generated by SQL Server for any table,
connection, or scope. If another insert takes place between the time of your
insert and the time when you check @@IDENTITY, @@IDENTITY will
return not your insert, but the last insert. For this reason, don’t use @@IDENTITY.
Example: Suppose you have table category and
a insert trigger on category table. This trigger is used to insert identity
value in Product table. Then @@IDENTITY returns the last inserted
value from Product table.
---------------Session
1---------------
If OBJECT_ID('Category','U') is not null
Drop table Category
go
Create table
Category(CatID int not null identity(1,1),CatName varchar(20))
go
If OBJECT_ID('Product','U') is not null
Drop table Product
go
Create table
Product(ProdID int not null identity(1000,1),ProdName varchar(20))
go
------------Insert Trigger on Category Table----------
Create Trigger CategoryTrigger
ON Category FOR INSERT
AS
BEGIN
INSERT into Product(ProdName) values('Product1');
END;
---------insert in Category table----
insert into Category(CatName) values('PromDresses');
-------------Check value of both table-------
select * from Category
select * from Product
------------Check value of @@IDENTITY-------
select @@IDENTITY
---This will return 1000
2 SCOPE_IDENTITY
(): This is function. It returns the last generated
identity value within the scope of the calling batch or procedure rather than
identity created by any trigger or any udf (user defined function). It is the
safest way to determine the identity value you last generated.
----Check scope identity----
select scope_identity()
------This will return 1 last inserted value of
category table in current session
3 IDENT_CURRENT
(TABLE): This function returns the last identity
value of a specific table in any session and any scope.
-------Checking Ident_current Value-------
Select Ident_current('Category')
-----This will return 1-------
Select Ident_current('Product')
---------This will return 100--------------
Check all the values in new session(Open new
query window)
--------------Session 2----------------
Select @@IDENTITY -------It will return null
Select Scope_Identity()--------return null
Select Ident_current('Category')
-----This will return 1-----------------------
Select Ident_current('Product')
---------This will return 100----------------
