Zafar Iqbal

I’ve run across several applications where the use of IDENTITY values has caused some confusion, specifically in how the @@IDENTITY function operates given external influences. We’ve all likely been faced with how to grab the newly generated IDENTITY value from a SQL Server database table. And there are a 2 popular techniques that accomplish this with SQL Server:

1. the @@IDENTITY function
2. the SCOPE_IDENTITY() function

They both will return a newly generated ID value but the difference between the 2 is very important. Important enough that you could get back an ID that you did not desire. The @@IDENTITY function returns the last IDENTITY value that was generated in your connection. So if you are running a stored procedure that looks something like this (using the Northwind database):

CREATE PROCEDURE prRegion_Insert_1(    @description NCHAR(50))AS

INSERT INTO Region (RegionDescription) VALUES (@description)

SELECT @@IDENTITY

GO

Assuming that your database has no other actions that occur between the INSERT statement and the SELECT @@IDENTITY statement, then you will get back the newly created RegionID value for the new Region row. But what if there is n Insert Trigger, for example, on the Region table that inserts a record into another table that also has an IDENTITY column. (Perhaps it does this to audit data.) In this case, the sequence of events would be:

1. the stored proc fires
2. insert the new region record
3. the trigger fires
4. the trigger’s code inserts the audit record
5. the stored proc then executes the SELECT @@IDENTITY, which then returns the ID that was created by the last statement, which is the audit table’s ID

In this case, the ID returned is the ID value that was created by the insert statement inside of the trigger. You might say that you would never put a trigger on a table that inserts into a table with an IDENTITY column in the first place. And I’d believe you … However … other developers and database administrators may create a trigger in the future that does this. They might not know that you wrote a stored procedure (or series of them) that relies on this situation not occurring. The point is that “stuff” happens (polite way of saying it).

So what does SCOPE_IDENTITY() do? It returns the last IDENTITY value generated in the scope. So what is the scope? The scope is a batch of SQL code such as s stored procedure, a trigger, or a user defined function. Using the previous example, the scope is the context of the stored procedure and not anything that gets called indirectly by it such as a trigger. This allows you to grab the last generated ID in the stored procedure, as you likely intended for it to happen. Here is another stored procedure that uses the SCOPE_IDENTITY() to grab the last ID generated in the Region table.

CREATE PROCEDURE prRegion_Insert_2(    @description NCHAR(50))AS

INSERT INTO Region (RegionDescription) VALUES (@description)

SELECT SCOPE_IDENTITY()

GO

Is it magic? Nah. But sometimes the smallest things can have huge impacts on our applications.