Finding the Lost Identity

Editor's Note: Congratulations to Scott Coutant at RDA Custom Software and Robertson Garcia of Pfizer Animal Health, who submitted the T-SQL Black Belt solution that contributing editor Itzik Ben-Gan based this article on. They'll each receive $100 and a T-SQL Black Belt shirt. Send your experts-only T-SQL tips to Itzik Ben-Gan at blackbelt@sqlmag.com.

The IDENTITY property provides auto-generated values for a numeric column in your table based on initial (seed) and increment arguments that you supply. The first row that users insert into the table contains the value you supply as the seed argument in the identity column. As users insert new rows into the table, SQL Server increments or decrements the identity value according to the value you supply as the increment argument. For example, you can use the IDENTITY property to automatically generate order IDs in an Orders table instead of supplying explicit IDs. As you insert new rows into the table, you can use the @@identity function to retrieve the last identity value you inserted. In this example, after you insert a new order into the Orders table, you can retrieve that order's ID by querying @@identity, so you can add new order parts to an OrderDetails table.

This method works fine in most cases, but sometimes after you add a new row to a table with an identity column, the @@identity function doesn't reflect the correct new identity value. In this article, I discuss situations that result in an incorrect @@identity value, and I present a few solutions, including a very cool one that Scott Coutant of RDA Custom Software and Robertson Garcia of Pfizer Animal Health sent.

Orders and OrderDetails Scenario
Listing 1 shows the script that creates the Orders and OrderDetails tables. Suppose you want to insert a new order into the Orders table, then retrieve the new order ID by querying the @@identity function so that you can add new order parts to the OrderDetails table. Listing 2 shows how your script might look. After running the script that Listing 2 shows, you'll have one order in the Orders table with an order ID of 1 and three order parts in the OrderDetails table for that order, as Figure 1, shows.

Suppose you need to start logging new orders in a separate table because orders are updated in the Orders table and you need to maintain a copy of their original form. You need to keep an auto-incrementing log ID for each logged order. You create a new table called LogOrders and a trigger that writes log rows to the LogOrders table each time users insert new orders into the Orders table, as Listing 3, page 26, shows. Note that the LogOrders table has its own IDENTITY column, which I defined with a seed of 1000.

Now, try to run the script in Listing 2 again to make a new order. You'll get error 547 three times, which means that you're trying to violate the foreign key between the OrderDetails and Orders tables. The errors occur because of activity inside the new trigger. As the script inserted the new order (ID 2), the @@identity value changed to 2, but before SQL Server executed the next statement, the trigger's code ran. As the trigger logged the new order in the LogOrders table, the value of @@identity changed to 1000. The script in this example tried to insert new order parts with an invalid order ID—1000. These attempts failed because the value of @@identity changes each time the session inserts a new row to a table with an identity column, regardless of which table the session tries to insert into or the scope from which the insert is performed. In this case, the @@identity value was last changed by an insert in the trigger's scope.

One way to handle this problem is to change both the script that you use to make the new order and the trigger that logs the new order. Before you insert the new order, you can create a temporary table with one integer column. The trigger's job is to check whether this temp table exists; if the table does exist, the trigger saves the current @@identity value before logging the new order. Note that SQL Server will save the new order ID in the temporary table before the trigger changes the ID. After the script for this solution inserts the new order, the script can retrieve the new order ID from the temp table instead of retrieving the incorrect @@identity value. Web Listing 1, which you can find by entering InstantDoc 9736 at http://www.sqlmag.com/, shows the trigger and the changed script.

The solution that Scott Coutant and Robertson Garcia presented is even cooler. They created a stored procedure, which I've named ResetIdentity, that accepts an integer value as a parameter and uses the IDENTITY function (not the IDENTITY property) in a SELECT INTO statement. Listing 4 shows the procedure. This statement creates a temporary table with an identity column that has the same seed value as the value that the parameter supplied to the stored procedure. The SELECT INTO statement creates a temporary table (with one row and one column) that holds the value that the stored procedure accepts as a parameter. Note that this statement also changes the @@identity value to the value of the stored procedure's parameter. You don't need to change the original script at all. You need to change only the trigger. First, the trigger needs to save the new order ID in a local variable before it logs the new order. After the trigger logs the new order—but before it finishes—the trigger calls the ResetIdentity stored procedure, which in turn changes the @@identity value to the saved order ID.

Note that this solution doesn't issue a CREATE TABLE statement to create the temporary table because SQL Server versions earlier than 2000 don't allow this approach. Instead, this method uses the SELECT INTO statement, which pre-2000 versions allow. Simply perfect! Listing 4 shows the script to create the stored procedure and revise the trigger.

Microsoft elegantly solved the lost identity value problem in SQL Server 2000. The @@identity function works the same as in earlier versions, but the product supplies two new functions: SCOPE_ IDENTITY( ) and IDENT_CURRENT('table_name'). SCOPE_ IDENTITY returns the last identity value that the session inserted into any table with an identity column in the current scope. SQL Server 2000 considers a trigger that fires as a result of an insert statement to be in a different scope from the insert statement that caused it to fire, so the trigger doesn't affect the value of SCOPE_IDENTITY that the insert statement's scope queries. This solution doesn't require changing the original trigger in the Order and OrderDetails scenario, as both solutions in this article did. You can use the IDENT_CURRENT function to retrieve the last identity value inserted into a specific table, regardless of session or scope.

• Scott Coutant works for "RDA" not "RDA Custom Software".