Many SQL Server applications utilize identity columns to automatically generate sequential IDs. Developers often use the @@IDENTITY function to retrieve the value of the last generated ID. This value is often passed back from a stored procedure with the RETURN statement or as an output parameter.
However, many developers still don't know that @@IDENTITY may not return the ID you're expecting. The @@IDENTITY contains the last generated identity value on the current connection (or NULL if there aren't any). If your insert statement adds a row into TableA and you call @@IDENTITY right after the insert statement, the function may or may not return the new ID from TableA. Most of the time it will have the value you're after, but there is one scenario where the value could be different. If the TableA has an INSERT trigger defined and the trigger inserts one or more rows into TableB and the TableB has an identity column, the @@IDENTITY return the last ID from TableB. As one of the developers on my team recently discovered, this behavior can introduce some subtle and hard to find bugs once you add a trigger to a table that uses an identity column.
So how do you get the right ID? If you're still using SQL Server 7.0 or lower, you're out of luck. SQL Server 2000 introduced a new function called SCOPE_IDENTITY. This function returns the last identity value within the same scope. The scope could be a batch, stored procedure or function. In the above scenario, calling SCOPE_IDENTITY right after inserting into TableA always returns the ID from TableA because the trigger on TableA is considered to be a different scope than the calling code.
It has become a good and recommended practice to start using SCOPE_IDENTITY instead of @@IDENTITY in SQL Server 2000 applications. You should consider doing the same even if you're not using triggers because you never know what requirements will be introduced down the road.