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.

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.