Don't use @@Identity

06 Apr 2011

Reading time: 2 minutes

Okay, I just got burned by an @@Identity bug in SQL Server. What is that? I had a written a spec that made it necessary for the developer to create a row in a database and return the ID to the application, then this ID would trigger an email to be sent in the site. Suddenly it breaks, why? Check out the @@Identity anti-pattern CREATE TABLE main ( id INT IDENTITY(1, 1), name VARCHAR(50) ) CREATE TABLE audit ( id INT IDENTITY(1, 1), name VARCHAR(50) ) INSERT INTO main (name) VALUES ('Hello') SELECT 'Number 1',@@IDENTITY INSERT INTO main (name) VALUES ('World') SELECT 'Number 2',@@IDENTITY GO Output is correctly showing "Number 1, 1", "Number 2, 2" So a new development requires the data to be transformed for some fast access audit tables elsewhere in the site. So we put in a trigger to deal with this new requirement. CREATE TRIGGER trig ON main FOR INSERT AS INSERT INTO audit (name) VALUES ('Please ignore this item') GO INSERT INTO main (name) VALUES ('ID number 3') SELECT 'Number 3', @@IDENTITY Output is "Number 3, 1" - not "Number 3, 3", WHY? Well the @@Identity function returns the Last inserted Identity. Because the trigger has caused another ID to be inserted in the database before the next line is run. INSERT INTO main (name) VALUES ('ID number 4') SELECT 'I expect ID number 4',Scope_identity() Output is "Number 4, 4". Scope_identity only includes the last identity from the scope you are running in. I fixed the rouge procedure, and then ran the excellent SQL Cop tool which is a small executable that searches your database to find common SQL issues. We had another 78 procedures in the database using the bad Identity syntax. Oh well, long evening for me, and a nice educational email to all the Devs. You know - I cannot think of a reason when you would want to get the ID of a value outside the scope, sounds like some crazy coding if you ask me! Now cleanup after yourself. DROP TRIGGER trig DROP TABLE main DROP TABLE audit