A common need when writing insert queries is to return the last Id that was generated when inserting your record. There is more than one way to retrieve this value and it can come back to bite you if you're not careful. In simple applications it's fine and simple to just append Select @@Identity to the end of your query. If you manage the database and know the structure will never change this isn't a problem. In larger scale applications, a DBA can make a simple change that can have major implications. I'm going to demonstrate the problem and show you how to avoid this pitfall.
Imagine the following scenario: You're building a simple shopping cart and you're allowing users to register before checking out. You have an Accounts table that has the following columns: AccountId, FullName, City, State, Zip. Obviously there is much more important data that you'd want to collect, but this is a simple example. You will need the AccountId when moving forward with the checkout process so you might write the following query to insert the new user data and return the new Id:
INSERT INTO Accounts (FullName, City, State, Zip) VALUES ("Foo", "Bar", "Omaha", "NE", "68154"); SELECT @@IDENTITY;
Executing this query would do just as you would expect. Since AccountId is an auto-increment field, this would return the ID that was generated. So if you're seed started at 1 and you were incrementing by 1 and this was the 5th record inserted, this would return "5". Throw it into production and all is well.
Now imagine the following scenario: After a year of operation, for some reason the store owner decides that every time someone from Iowa registers an account they receive a special promotional item that's added to the cart. Instead of calling the programmer, they just ask the DBA to add a trigger to the database. So the DBA adds a After Insert trigger to the accounts table and when State = "IA" it automatically adds a new promo item to the shopping cart table. Let's call this table Basket and it has a BasketId as a primary key which is also an auto-increment value.
Now when you execute the example query above, everything works exactly as it should. However, what happens when you execute the following:
INSERT INTO Accounts (FullName, City, State, Zip) VALUES ("John", "Doe", "Somewhere", "IA", "55555"); SELECT @@IDENTITY;
Here we're going to run into a scope issue because @@IDENTITY returns the absolute last generated Id, not the first generated Id. In other words, while your code expects AccountId to be the value returned, it will actually return BasketId since the trigger added a new item to the basket. Oh no!
Enter SCOPE_IDENTITY
What you were really after in the example above was AccountId. It's very simple to return the last Id added to the Accounts table by replacing @@IDENTITY with SCOPE_IDENTITY(). So the above query would now look like this:
INSERT INTO Accounts (FullName, City, State, Zip) VALUES ("John", "Doe", "Somewhere", "IA", "55555"); SELECT SCOPE_IDENTITY();
Calling scope identity returns the Id generated by the most immediate command in your scope (your insert statement). So when new Id's are needed, make sure you know what could potentially change with the structure in terms of triggers before using @@IDENTITY. Awareness of your data can save you endless hours of troubleshooting code.