@@Identity usage in SQL Server

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 200
@@Identity returns the last auto incremented or identity value for any table in the current session over all scopes. means that Its a GLOBAL identity value...

Example for explaining ALL SCOPES meaning...

Suppose that you have a table called Table1 and one INSERT trigger on Table1. The trigger logic is to insert same record into another table called Table2.

Script for above scenario is as follows:

CREATE TABLE Table1 ( ID int identity( 1,1), Name varchar(100))
GO
CREATE TABLE Table2 ( ID int identity( 10, 1) Name VARCHAR(100))
GO
CREATE TRIGGER Tri_InsertTrigger
ON Table1
FOR INSERT
AS
INSERT Table2 SELECT name from inserted
GO


If we insert one record into Table1, then what will be the IDENTITY value now ?

INSERT Table1 VALUES( 'Bandi')
SELECT @@IDENTITY [IdentityValue?]
SELECT ID Table1ID FROM Table1

Output:
IdentityValue? 
10

Table1ID
1


It is very clear that the last identity value of Table1 is 1. but @@IDENTITY returned 10 as identity value. means that the 10 is the identity value of last INSERT statement ( i.e. Table2's INSERT statement in TRIGGER)...

@@IDENTITY is global system variable, which returns IDENTITY value across all scopes( here the first table Table1, the trigger Tri_InsertTrigger and Table2).

Comments or Responses

Login to post response