Alternatives to @@IDENTITY in SQL Server 2000By Sean Baird on 19 September 2000 | Tags: Identity Mark writes: "I was asked a question today by a developer having problems getting the right identity value from @@identity when a table has a trigger which has an additional insert - Post the insert statement the select @@identity returns the wrong value (which is behaviour I would expect).
SQL Server 2000 has three functions that return
IDENTITY information. The result of each of these three functions is dependent on three factors:
INSERT that fires a trigger, I have two different scopes: scope 1 is inside the batch that called the INSERT , and scope 2 is inside the trigger.)SELECT @@IDENTITY This is everyone's favorite function, unchanged from earlier versions of SQL Server. It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.SELECT IDENT_CURRENT('tablename') This new function returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.SELECT SCOPE_IDENTITY() This new function returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.Okay, I think these are best explained with an example, and since Mark has provided a good one, we'll use it. Let's create some tables: This is a basic scenario that duplicates the problem Mark described. Whenever I insert a record(s) into YakName, the trigger will insert a record(s) into YakTracker (we need audit information on the Yaks, apparently).Now, Mark's developer is trying something like this: INSERT YakName VALUES ('Graz') Although what we really want from this batch of statements is the last IDENTITY value for the YakName table, we get the value 1000--the last IDENTITY value for the YakTracker table. Why is this? The second insert statement in the trigger also inserts into a table with an identity. Remember, @@IDENTITY works across all tables (YakName, YakTracker) and all scopes (batch scope, trigger scope), so it picked up the change we made to a different table (YakTracker) in a different scope (the trigger)! Prior to SQL Server 2000, we would have been stuck. At this point, we'd have to eliminate the IDENTITY column on the YakTracker table to get these statements working the way we want them to.But let's say we've shelled out the cash for the latest copy of SQL Server 2000. Let's look at how the new functions would behave: INSERT YakName VALUES ('Billy Joe Bob') SCOPE_IDENTITY() works for all tables in the scope for which it was called, which in this case is the original batch. So, we get the last value for the YakName table, which is what we wanted.Note that I included some samples for IDENT_CURRENT as well. Unless someone else on another connection is also inserting values into the YakName table, you will get the results shown in the example. (Remember that the IDENT_CURRENT function disregards which connection produced the last IDENTITY value for the specified table.)Admittedly, these are some pretty handy functions. Of course, I saw these and wanted more... Wouldn't it be nice to have a rowset function that returned all of the IDENTITY values created because of a multi-row insert? Oh well, I guess we have to wait through another couple of versions to see that feature. :)-SQLGuru
|
- Advertisement - |