AzamSharp

When trying to retrieve the identity of the last row inserted in the table always use SCOPE_IDENTITY instead of @@IDENTITY. If you are simply inserting data into the database table using a simple INSERT query than you won't be able to see the disaster behind using @@IDENTITY. But as soon as you or your fellow developer start using triggers and tries to insert data into another table depending on the insertion in the first table the whole hell will break loose. Here is a small scenario. You write a query to insert data into table1, a trigger is fired which is used to insert data into table2. Now if inside your query you retrieve @@IDENTITY you will not get the identity of the last row inserted in table1 but you will get the identity of the last row inserted in table2 since technically that was the last identity that was generated and which offcourse you were not expecting. If you want to get the identity from table1 than you will use SCOPE_IDENTITY. Simply, @@IDENTIY will return you the last ID generated in any table. If you are firing triggers on 10 tables and if you do @@IDENTITY than it will return you the identity from the table10. I will soon write a small article on this topic.