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:

  1. <FONT face=Tahoma>the @@IDENTITY function
  2. <FONT face=Tahoma>the SCOPE_IDENTITY() function
  3. <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:

    1. <FONT face=Tahoma>the stored proc fires
    2. <FONT face=Tahoma>insert the new region record
    3. <FONT face=Tahoma>the trigger fires
    4. <FONT face=Tahoma>the trigger’s code inserts the audit record
    5. <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
    6. <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> 

Tweet Post Share Update RSS

Hi, I'm John Papa. I author this blog, create courses for Pluralsight and am a Google Developer Expert and Microsoft Regional Director. I travel speaking at events and train technology thought leaders

Tags:
All