Often I see people just using @@IDENTITY when needing the latest used identity value inserted into a table.
However this is dangerous behavior seeing as a potential bug resides there.
As mentioned in the documentation for the functionalities @@IDENTITY returns the last used identity value within the given session, but the globally used identity. This means that if something else, within the same session (not to be mistaken with a HTTP session ;) ) uses identity insert, you risk getting the wrong value back.
SCOPE_IDENTITY() only returns within the same scope.
One exists called IDENT_CURRENT which can return the identity for any session for a given table. However, I’ll only focus on @@IDENTITY and SCOPE_IDENTITY() as they are most common.
An easy way to see the difference is to make two tables with an identity column and a dummy value field like this.
CREATE TABLE TableOne(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Value] [nvarchar](50) NULL
)
CREATE TABLE TableTwo(
[ID] [int] IDENTITY(10,1) NOT NULL,
[Value] [nvarchar](50) NULL
)
Note that the second table has a different initial identity value then TableOne. This will help with the illustration.
Now create a trigger on TableOne to run after Insert which inserts another row into TableTwo. Just some dummy data like this:
CREATE TRIGGER [dbo].[TriggerInsert]
ON [dbo].[TableOne]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO TableTwo VALUES ('ss')
END
GO
Now try running the following query to illustrate the difference between the two IDENTITY functionalities:
INSERT INTO TableOne VALUES ('test')
SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()
You’ll notice that the first SELECT @@IDENTITY returns a different identity value then just inserted into TableOne, and that is because it takes the last used identity value for the given session. SCOPE_IDENTITY() returns the expected value.
This is a potential problem if not aware of the differences and for example triggers are used. It can often provide in foreign key integrity problems and can be difficult to debug if multiple developers work on the same database.