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 scope of the current procedure, 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.
But
what happens if, sometime in the future, another developer, who is unaware
that @@IDENTITY is being used, adds a trigger that adds an Audit log record.
All of
a sudden, your application will break 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 caneliminate
the issues associated with @@IDENTITY. Unfortunately, in a busy environment,
you can't be sure that between the execution of your INSERT command and the
retrieval of IDENT_CURRENT, that a different record wasn'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.
The scope would typically be a 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.
Comments