My take on @@IDENTITY vs SCOPE_IDENTITY()

This may be considered to be somewhat of a ‘classic’, but as mentioned, many of the entries in this blog are reminders and/or point of reference for myself.

Which I am of course happy to share. This one is about a question that comes every now and then;

“There is something wrong with @@IDENTITY, we have been using this for some time and it has always correctly returned the identity value of the last inserted row.

Now it returns what seems to be random numbers. Also, it seems to work sometimes and fails other times. This must be some sort of a bug”.

The short answer here is do not use @@IDENTITY for this purpose, use SCOPE_IDENTITY(). There is a subtle difference between these.

And the behavior described above is usually an effect of using @@IDENTITY instead of SCOPE_IDENTITY() in combination with triggers.

As usual, I’ll show this behavior by example since this makes it easier to understand.

First we need two tables (with some rows) and a stored procedure that takes care of inserting rows to one of the tables.

This procedure will return the new identity of the row being inserted. The emptype here tells us if the employee is a Manager or not.

So from SQL Server Management Studio, run the following:

create table Employee (id int identity, empname nvarchar(100), emptype int)

create table Manager (id int identity, empid int)

insert into Employee values ('John', 0)

insert into Employee values ('Paul', 0)

insert into Employee values ('Jane', 0)

insert into Employee values ('Mike', 1)

insert into Employee values ('Mary', 1)

go

create procedure InsertPerson(@empname nvarchar(100), @emptype int)

as begin

               insert into Employee values (@empname, @emptype)

               declare @newId int

               set @newId = @@identity

               return @newId

end

Check the number of rows in the Employee table (select * from Employee), this should return 5 rows, with 5 being the highest id.

Test the stored procedure:

declare @newId int

exec @newId = InsertPerson 'Neil', 1

select @newId

This should return 6, recheck the number of rows in the Employee table, this should now return 6 rows. So at this stage all is well.

Create a new C# console application that uses the stored procedure in order to insert new Employees. Code as follows:

        static void Main(string[] args)

        {

            string cs = @"Data Source=<your server>;Integrated Security=TRUE;Initial Catalog=<your database>";

            using (SqlConnection con = new SqlConnection(cs))

            {

                try

                {

                    con.Open();

                    SqlCommand cmd = new SqlCommand("InsertPerson", con);

                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    cmd.Parameters.Add("@empname", SqlDbType.NVarChar).Value = "Pete";

                    cmd.Parameters.Add("@emptype", SqlDbType.Int).Value = 1;

                    cmd.Parameters.Add("@newId", System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.ReturnValue;

                    cmd.ExecuteNonQuery();

                    int newId = (int)cmd.Parameters["@newId"].Value;

                    Console.WriteLine("The returned id is {0}", newId);

                    con.Close();

                }

                catch (Exception ex)

                {

                    Console.WriteLine(ex);

                }

            }

        }

And run it, this should return 7.

Change the name (Pete) to something else and rerun, this should return 8.

So now your application works. You can successfully insert new employees and get back the identity of that new row.

Your application runs fine.

Now, later on someone decides that when a new employee is added, we should also insert a row into the Manager table if the Employee emptype is 1 (which is Manager in this example).

This is solved by using a trigger. So in SSMS add the following trigger:

create trigger MgrTrigger on Employee after insert

as

if (select emptype from inserted) = 1

begin

               declare @id int

               set @id = (select id from inserted)

               insert into Manager(empid) values(@id)

end

Try this by inserting one row into the Employee table which has emptype = 1 and one row which has emptype = 0

insert into Employee values ('Timm', 0)

insert into Employee values ('Anna', 1)

Now, select * from Employee and select * from Manager.

You should have 10 rows in the Employee table and 1 row in the Manager table, so this seems to work fine.

Go back to the C# application, change the name to something new and rerun it.

What do you expect the return value to be now? There are 10 rows in the Employee table, so it should be 11. Right?

Wrong, the returned value is actually 2.

So you check the number of rows in the Employee table, this returns 11 and the last name is the one you added.

So where does the value 2 come from?

Well, this is where the subtle difference between using @@IDENTITY instead of SCOPE_IDENTITY() comes into play.

If we check the documentation we can see the following:

After an INSERT, SELECT INTO, or bulk copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. If a trigger is fired after an insert action on a table that has an identity column, and the trigger inserts into another table that does not have an identity column, @@IDENTITY returns the identity value of the first insert. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.

Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions because they all return the last value inserted into the IDENTITY column of a table.

@@IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.

What this then means is that since we have introduced a trigger that does a conditional insert (if emptype is 1) it means that @@IDENTITY in this case returns the last generated identity value

for the Manager table, not the Employee table. This also means that if you change the C# code to insert an Employee that is not a manager (emptype = 0) it will again seem to work as expected.

Whether to use one or the other, I’ll leave that decision to you. This post is only about explaining and exemplifying what is going on.

If you need more guidance, a simple search on SCOPE_IDENTITY and @@IDENTITY will give a lot more information.