Identifying New Records
Posted: Tuesday, November 25, 2003 10:53 AM
by
bruce
Filed under: SQL Tips
While the use of AutoNumber and Identity fields in tables is greatly applauded by database designers the world over, they can be annoying for developers to use. The problem being that, when we add records to the database, there are many cases where we need to know the key of the record that has just been added. Fortunately, there are a number of techniques that are available to assist us in our task. In this article we look at the methods for both SQL Server and Access, with an eye to the pros and cons of each approach. As always, our goal is to give you enough information to make up your own mind
SQL Server
SQL Server 2000 offers three, count 'em, three options for retrieving the Id of a recently added record. The 'best' method depends on what you're doing and what else might be going on in your database. In all cases, the Id is a field marked as the primary key of a table and that has the Identity property set to True.
| @@IDENTITY |
This is an intrinsic variable in SQL Server that contains the Id of the record that was last created on this connection, regardless of the table. |
| IDENT_CURRENT('table') |
This function returns the ID of the record that was last created in the specified table. |
| SCOPE_IDENTITY |
This variable contains the Id of the last record that was created within the current scope, regardless of the table. |
So what are the ramifications of using each of these options? With @@IDENTITY, you get the most recent identity on the current connection. That means that if, as part of the INSERT statement, triggers get executed that in turn INSERT records into other tables, @@IDENTITY contains the last Id that was generated. Regardless of the table into which the record was inserted. Now if you created all of the stored procedures, triggers and INSERT statements yourself, that is fine. You can control when the @@IDENTITY value is being checked. But what happens if, sometime in the future, another developer, who is unaware that @@IDENTITY is being used, adds a trigger that creates an Audit log record. All of a sudden, @@IDENTITY returns a different id. And not the one for the just added record. As a result, your application breaks even though 'nothing' has changed. And that is the kind of bug that we all just love to track down.
The IDENT_CURRENT function is best used when you can control who has access to the database and when. By specifying the table as part of the function, you can eliminate the issues associated with @@IDENTITY. At least as far as the addition of records in triggers goes, However IDENT_CURRENT works at the table level, not the connection level. It returns the most recently created id, regardless of who created it. Unfortunately, in a busy environment, developers can't be sure between the execution of the INSERT command and the retrieval of IDENT_CURRENT, a different record isn't inserted by another user.
The SCOPE_IDENTITY instrinsic variable addresses some of the issues raised with the other two methods. Its value is the last Id created within the current scope. Athough technically the scope is the current connection, practically, the scope is more typically the currently executing stored procedure. Now you don't have to worry about the possibility of future enhancements 'breaking' your code, nor do you have to deal with other activity impacting the Id that is returned. If you perform more that one INSERT in a stored procedure, you do need to use the SCOPE_IDENTITY between each statement in order to retreive all of the created Id's. But again, that is within your sphere of control.
Unfortunately, if you are using SQL Server 7.0 or earlier, then the @@IDENTITY method is the only choice available to you. Both IDENT_CURRENT and SCOPE_IDENTITY were introduced with SQL Server 2000.
Microsoft Access
With Access, you are limited to basically a single technique. On a positive note, the same technique works all the way back to Access 97
First of all, I assume that we are inserting a record into a table where the primary key has an AutoNumber type. The addition of the record must be accomplished by using the AddNew and Update methods of the ADO Recordset object. Then, once the record has been added, store the absolute position of the new record and perform a Requery. Finally, set the cursor back to the bookmarked record and read the Id. Seem like a lot of work? And inflexible to boot? Such is the joy of Access
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open "DSN=MyDSN;"
rs.CursorLocation = adUseClient
rs.Open "SELECT CustNo, CustomerName, Contact", cn, adOpenStatic, adLockOptimistic
rs.AddNew
' CustNo is the AutoNumber field
rs.Fields("CustomerName").Value = "ObjectSharp"
rs.Fields("Contact").Value = "Bruce Johnson"
rs.Update
' The record has been inserted, but rs.Fields("CustNo").Value is zero
bookmark = rs.absolutePosition
rs.Requery
rs.absolutePosition = bookmark
' Voila
MsgBox rs.Fields("CustNo").Value
If you would like to receive an email when updates are made to this post, please register here