Using @@Identity

The @@Identity variable is an intrinsic variable which contains the last value inserted into a primary key via the auto increment method.

What this means is that if you create a table that has an identity column as a primary key, once that identity column is inserted anywhere within that connection then the @@identity variable will hold that value.

You may be wondering what the catch is with the italic bold statement above stating “anywhere within that connection”. Well, it is a catch because this is what can make the @@identity variable so dangerous.

Here’s the scenario. Say you perform an insert into the customers table.

[cc lang=”sql”]
INSERT INTO Customers
(
FirstName,
LastName
)
SELECT
FName,
LName
FROM dbo.NewCustomers
WHERE CutomerID = 1

SELECT @@identity
[/cc]

According to this example, the @@identity will surely contain the value of the primary key which is the auto increment for the Customers table right?? Hmm.. maybe.. and most likely… but maybe not.

Here’s why. If the Customers table fires a trigger to insert into say, an audit table, and that audit table has an auto increment primary key, then you might find that auto increment returned into the @@identity variable.

This is because the trigger is executed in the context of the same connection as the original insert. And remember, @@identity contains the most recent identity column from anywhere within the connection.

So what do you do? Use the Scope_Identity() function. It will only return the identity column inserted from the last batch (or statement).

[cc lang=”sql”]
INSERT INTO Customers
(
FirstName,
LastName
)
SELECT
FName,
LName
FROM dbo.NewCustomers
WHERE CutomerID = 1

SELECT SCOPE_IDENTITY()
[/cc]