SCOPE_IDENTITY vs. @@IDENTITY
17-May-07
I stumbled across some extremely handy information today, looking for something completely different.
The deal is such:
When writing your CRUD operations, often-times you'll find yourself writing SQL that looks something like this:
-
CREATE PROCEDURE dbo.app_CreateSomeDataPlease
-
(
-
@someData nvarchar( 1024 ),
-
@moreData nvarchar( 2048 ),
-
@irrelevantNumber int = 42
-
)
-
AS
-
BEGIN
-
INSERT INTO
-
MyTable
-
(
-
SomeData,
-
SomeMoreData,
-
IrrelevantNumberForNoGoodReason
-
)
-
VALUES
-
(
-
@someData,
-
@moreData,
-
@irrelevantNumber
-
);
-
-
SELECT @@IDENTITY AS NewDataId;
-
END
-
GO
Which is all good and well (obviously a contrived and simple example). You expect to get back the new ID for the item you just created, right?
Maybe not.
Y'see, there are these things called "Triggers". You've heard of them, maybe played with them, whatever. Basically, a Trigger is SQL that gets "triggered" when something happens, like, perhaps, an INSERT, UPDATE or DELETE.
Now, I'm not going to debate whether Triggers are good, bad, or Satan in disguise (they're not). The point here is that, uh-oh - if you have an INSERT trigger on the table you're creating your data in, and that Trigger creates a row (or rows) in another table (or tables), your @@IDENTITY is not going to do what you think it's going to do.
Why not? Hang on, we'll get there.
There's another method of retrieving the last identity value, and that is the SCOPE_IDENTITY function. And the very, very subtle difference between SCOPE_IDENTITY() and @@IDENTITY is enough to trip you up for hours on end (if you hadn't read this post, of course - bear with me, we'll get there).
So what is the difference between SCOPE_IDENTITY() and @@IDENTITY?
Scope.
And I don't mean the mouthwash.
To quote the MSDN documentation:
SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.
A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.
What that means is that if have a trigger that fires on INSERT (into Table1) and INSERTs into another table (let's call that Table2), returning @@IDENTITY will return the new identity value from Table2, not Table1.
Maybe that's what you want, but probably not.
To make a long story short, to be safe, you're probably better off using SCOPE_IDENTITY to protect yourself in the event that a Trigger you don't know about* is firing and inserting somewhere else. And if you know about the Trigger and you want the ID from that, use @@IDENTITY.
Read a bit more about SCOPE_IDENTITY at the MSDN.
* - In all unfairness, you should know which triggers (if any) are firing, but...hey...someone else on your team may add one later on and suddenly your app breaks...now you'll know why and how to fix it...
Intellisense (code-completion, for those of you who don't work with MS products) is one of those features that really make an IDE useful. We all no that we can't remember much (if anything), and having that little drop-box show up when we need it is a huge time saver.