SCOPE_IDENTITY() and @@IDENTITY Demystified
<FONT face=Tahoma>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:
- <FONT face=Tahoma>the @@IDENTITY function
- <FONT face=Tahoma>the SCOPE_IDENTITY() function
- <FONT face=Tahoma>the stored proc fires
- <FONT face=Tahoma>insert the new region record
- <FONT face=Tahoma>the trigger fires
- <FONT face=Tahoma>the trigger’s code inserts the audit record
- <FONT face=Tahoma>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
<FONT face=Tahoma>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):
<FONT color=#0000ff size=2>CREATE<FONT size=2> <FONT color=#0000ff size=2>PROCEDURE<FONT size=2> prRegion_Insert_1
@description <FONT color=#0000ff size=2>NCHAR<FONT color=#808080 size=2>(<FONT size=2>50<FONT color=#808080 size=2>)
<FONT color=#0000ff size=2>AS
<FONT size=2><FONT color=#0000ff size=2> INSERT<FONT size=2> <FONT color=#0000ff size=2>INTO<FONT size=2> Region <FONT color=#808080 size=2>(<FONT size=2>RegionDescription<FONT color=#808080 size=2>)<FONT size=2> <FONT color=#0000ff size=2>VALUES<FONT size=2> <FONT color=#808080 size=2>(<FONT size=2>@description<FONT color=#808080 size=2>)
<FONT size=2><FONT color=#0000ff size=2> SELECT<FONT size=2> <FONT color=#ff00ff size=2>@@IDENTITY
<FONT size=2>GO
<FONT face=Tahoma>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:
<FONT face=Tahoma>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).
<FONT face=Tahoma>
<FONT face=Tahoma>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.
<FONT color=#0000ff size=2>CREATE<FONT size=2> <FONT color=#0000ff size=2>PROCEDURE<FONT size=2> prRegion_Insert_2
@description <FONT color=#0000ff size=2>NCHAR<FONT color=#808080 size=2>(<FONT size=2>50<FONT color=#808080 size=2>)
<FONT color=#0000ff size=2>AS
<FONT size=2><FONT color=#0000ff size=2> INSERT<FONT size=2> <FONT color=#0000ff size=2>INTO<FONT size=2> Region <FONT color=#808080 size=2>(<FONT size=2>RegionDescription<FONT color=#808080 size=2>)<FONT size=2> <FONT color=#0000ff size=2>VALUES<FONT size=2> <FONT color=#808080 size=2>(<FONT size=2>@description<FONT color=#808080 size=2>)
<FONT size=2><FONT color=#0000ff size=2> SELECT<FONT size=2> <FONT color=#ff00ff size=2>SCOPE_IDENTITY<FONT color=#808080 size=2>()
<FONT size=2>GO
<FONT face=Tahoma>Is it magic? Nah. But sometimes the smallest things can have huge impacts on our applications.
<FONT size=2><FONT size=3>