Identity vs SCOPE_IDENTITY vs IDENT_CURRENT


It is a common practice to use IDENTITY value for columns which are simple integer increments. We can also specify what is the initial value and how we want to increment using seed values. In some cases the client applications needs to know what is the IDENTITY value inserted into the table. Consider a scenario of a new customer ordering some products in an ecommerce application. In this case we have to do the following things.

  1. We have to insert the Customer (assuming there is a IDENTITY column set for CustomerID)
  2. Select the CustomerID that was inserted in Step 1 and associate with Order in client application and  insert the Order record (assuming we have a IDENTITY value set for OrderID Columns as well)
  3. Select the OrderID from Step 2 and insert multiple records into the OrderDetail table

Before 2008, this simple process involves multiple round trips to the database and application for transferring the identity values. With the advent of Table Valued Parameters, now we can encapsulate all this information into a tables and sent to Stored procedures there by reducing the round trips. But still there exists one problem in getting the IDENTITY values for different tables. As we all know , for example, we cannot get consecutive values for IDENTITY if we have concurrent transactions running, can have gaps in IDENTITY if the server restarts etc. In the previous scenario of Cust-Order-OrderDetail we can get the last inserted value using @@IDENTITY function. This function gives you the last inserted  regardless of the scope. There is one more function called SCOPE_IDENTITY(). This function returns the last inserted value within the same scope. To clearly understand the distinction between these 2 lets fire up SSMS and run the following.

Create 3 tables : Customer, Order and Email( this will be used for trigger)

CREATE DATABASE TEST
GO

USE TEST
GO

IF OBJECT_ID('dbo.Customer', 'U') IS NOT NULL
  DROP TABLE dbo.Customer
 

IF OBJECT_ID('dbo.Ordertbl', 'U') IS NOT NULL
  DROP TABLE dbo.Ordertbl
  

  IF OBJECT_ID('dbo.Email', 'U') IS NOT NULL
  DROP TABLE dbo.Email
  


CREATE TABLE dbo.Customer
(
CustID smallint IDENTITY(100, 10) NOT NULL,
CustName varchar(50) NOT NULL
);

CREATE TABLE dbo.Ordertbl
(
    OrderID INT IDENTITY(10,10) NOT NULL,
    CustID smallint,
    OrderDate datetime
);

CREATE TABLE Email
(
    ID INT IDENTITY(10,50) NOT NULL,
    EmailMsg NVARCHAR(100)
)

Create a new trigger which will insert into the Email table for each successful order placed.

CREATE TRIGGER SendMail
ON Ordertbl
FOR INSERT AS 
   BEGIN
   INSERT INTO Email VALUES ('Order placed successfully')
   END

Now insert records into Customer and Order and observe the values for SCOPE_IDENTITY and @@IDENTITY

/*Insert into the Customer-Order table and observe the identity values 
with the @@IDENTITY and SCOPE_IDENTITY functions.*/
BEGIN
INSERT INTO customer VALUES('Bill Gates')
INSERT INTO customer VALUES('Steve Jobs')

INSERT INTO Ordertbl VALUES(100,GETDATE());
INSERT INTO Ordertbl VALUES(110,GETDATE());


end


SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
SELECT @@IDENTITY AS [@@IDENTITY]
GO

image

As you can see , there are 2 scopes with in the insert statements above. One, inserting into Customer and Order table and other inserting into Email table because of trigger. Thus the SCOPE_IDENTITY() returns the last identity value inserted , in this case (10+10=20 ; since we declared the initial seed as 10 and increment as 10 and inserted 2 records) and @@IDENTITY returns the last identity value inserted regardless of scope. i.e. from Email table (10+50=60).

But how will we identify the last inserted record in the specific table regardless of scope and session ?

We can use  IDENT_CURRENT in that case

SELECT IDENT_CURRENT('Customer');

To test this, open a new session window and run the following query

USE TEST
GO

INSERT INTO customer VALUES('Larry Elison')

Insert into Ordertbl VALUES(120,GETDATE())

Now if you come back to session window 1 and execute the following

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
SELECT IDENT_CURRENT('Ordertbl') AS [IDENT_CURRENT]
GO

The result is as follows

image

So use

IDENT_CURRENT : if you want to know the last inserted value into the table regardless of connection

SCOPE_IDENTITY : Know the last inserted value into the table with in the current session

@@IDENTITY: Use only if there are no triggers and  no concurrent users

 

Hope this article helps.

Advertisements
This entry was posted in SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s