SQL Server Brain Basher of the Week #042 - Identity property

Let's talk about an important property we always use; Identity property. This property has been used for generating Ids automatically, and most of the cases, this has been used as the surrogate key. Usage is very simple, all you have to do is, set it with CREATE TABLE statement, assigning the seed (starting value) and increment value. SQL Server will automatically generate a value for this when an insert is performed.

Since the value is not based on an application, we have no clue on the value generated. If we need the newly generated identity value for performing the next step of the process we have written, we used to call either @@identity or Scope_Identity function. Here is this week question based on it;

What is the different between @@identity and Scope_Identity function? Which one should be used for getting the last generated value within the scope?

Let's try to understand the purpose of these two. @@identity is a function that returns the last generated identity value regardless of the scope but for the current session. But Scope_Identity function returns the last generated value within the scope for the current session. That is the different between these two functions. For most cases, Scope_Identity is the best unless you have an unique requirement for getting the value via @@identity.

See this example, it shows how these two functions works.

-- Creating Customer table
-- Id is an identity, starting number is 1
CREATE TABLE dbo.Customer
(
 Id int identity(1,1) Primary key
 , Name varchar(100) not null
);

-- Creating Customer Log table
-- Id is and identity, starting number is 100
CREATE TABLE dbo.CustomerLog
(
 Id int identity(100,1) Primary key
 , Info varchar(100) not null
);
GO

-- This stored procedure accepts a name
-- and insert a record
CREATE PROC dbo.InsertCustomer @Name varchar(100)
AS 
BEGIN

 INSERT INTO dbo.Customer
 (Name) VALUES (@Name);

 -- Getting CustomerId value via @@identity
 SELECT @@IDENTITY AS CustomerIdFromIdentityFunction;
 -- Getting Customer ID via Scope_identity
 SELECT SCOPE_IDENTITY() AS CustomerIdFromScopeIdentityFunction;
END
GO


-- This trigger will insert a record to
-- CustomerLog table
CREATE TRIGGER dbo.InsertTriggerForCustomer
ON dbo.Customer
FOR INSERT
AS
BEGIN

 INSERT INTO dbo.CustomerLog
  (Info) VALUES ('Record is inserted');
END

-- Executing the first procedure
EXEC dbo.InsertCustomer 'Dinesh';

As you see, @@identity returns 100 and Scope_Identity returns 1. The reason for showing 100 for @@identity is, the insert happened inside the trigger. Trigger inserted a record and its Id is 100. Since @@identity for the current session, not for the current scope, it returned the last generated identity regardless of the scope.