@@IDENTITY vs. SCOPE_IDENTITY()

I'll admit, I've been guilty of using @@IDENTITY when I should have used SCOPE_IDENTITY(). A lot of people make this mistake. Let's take a brief look at them both and see what all the fuss is about.

First off, if you're not using Identity/seed values as the primary key in your tables...these functions/variables probably won't be of much immediate use to you. I always use identity columns as my primary key. We could go on and on about surrogate keys vs. natural keys....but let's not....not right now anyway. So we'll break this down into 3 sections:

1. Why we need @@IDENTITY or SCOPE_IDENTITY().

2. What's the difference.

3. A Simple example.

Why we need @@IDENTITY or SCOPE_IDENTITY()

The most common reason is probably to grab the identity value (usually the primary key) of a row you just inserted, so you can then use it as a foreign key on an insert or update in a different table later on. In order to ensure you're getting the ID of the value you just inserted, you need to use one of these.

What's the difference:

@@IDENTITY is a system variable that returns the last identity value produced on a connection with no regard to scope. The most common problem that arises from this is that if you have a trigger that fires and also inserts rows between your insert and your call to @@IDENTITY...you'll get the wrong ID. It's pretty easy to imagine how that's problematic.

SCOPE_IDENTITY() on the other hand is a function that will return the last identity value produced on a connection within scope. Since a trigger firing due to your insert is outside the scope of your procedure, you'll still get the correct ID.

The takeaway here is that you should probably be using SCOPE_IDENTITY() and not @@IDENTITY unless you're on a version of SQL Server that only supports @@IDENTITY (which I believe is the case with SQL Server 7.0 and SQL Compact Edition)

A Simple Example:

--check the max ID from games(just to show you what it is)

SELECT MAX(ID) 'ID' FROM Games

--insert a new game

INSERT INTO

      Games

      (

      SystemID,

      Name

      )

VALUES

      (

      7,

      'Dance Central'

      )

     

--show the identity value for the row i just inserted

SELECT SCOPE_IDENTITY() 'NewRowIdentity'

The above code would produce the following results:

ID

-----------

7

(1 row(s) affected)

(1 row(s) affected)

NewRowIdentity

---------------------------------------

10

(1 row(s) affected)

So we can see that before my insert the highest identity value in my "Games" table was 7. So we expect the next value to be 8 right? Then why is it 10? It's 10 because I made sure to rollback a few transactions to prove a point. You can't assume you know what the next identity value is going to be based on the current max value. Once an identity value is generated, it doesn't get generated again (unless you manually reset the seed value). So if someone deleted a row, or a row was inserted but rolled back in a transaction, those id's aren't going to be generated again.

Anyway, hopefully this helps you keep your dAtaZ in line. Questions/comments to tavis.lovell.blog(at)gmail.