When a row is inserted into a table that has a primary key column, whether by a stored procedure or a passed command, there may
Requires Free Membership to View
be circumstances where the user or programmer needs to know the newly generated primary key right away. This is typically the case where the primary key is used to perform JOINs to other tables, and the rows in those other tables need to be inserted or updated immediately.
SQL Server 7 and 2000 provide ways to know what the most recently-inserted identity key is from a given operation, but they all function a little differently in that each of them uses a different scope for their operations. For that reason, they're all suited to different tasks.
The most commonly used method (available in SQL Server 7 and lower) is the @@IDENTITY variable. @@IDENTITY retrieves the last-generated identity key, wherever it may have been generated. For instance, if you run a stored procedure that fired a trigger which generated an identity key, @@IDENTITY would be set to the identity key produced by the trigger. Note that if the identity key was created within the context of a transaction, rolling back the transaction would not roll back the value of @@IDENTITY, so any tracking of such keys through the context of a transaction has to be done manually.
SCOPE_IDENTITY works a little differently; it retrieves an identity key that is confined to the scope of the current transaction, procedure or command. This makes it more useful for getting the most immediately relevant results (especially in the context of a stored procedure).
IDENT_CURRENT has the broadest scope (it works in any session or transaction), but returns the most recent key results generated for a specific table. The name of the table is passed as a parameter. This way you can directly track key results for a given table, anywhere, no matter who or what does the inserting—the current procedure or command, a remotely-fired trigger, etc.
One good piece of programming hygiene to use in conjunction with any of these commands is the use of SET NOCOUNT ON before and SET NOCOUNT OFF after retrieving the identity key. This keeps SQL Server from maintaining a running count of the number of rows affected by a statement, and can save memory and processing power—especially if many transactions are taking place at once, and the row count is not crucial to that particular operation.
Serdar Yegulalp is editor of the Windows 2000 Power Users Newsletter. Let us know what you think about this tip; email editor@searchcio-midmarket.com.
This was first published in December 2004
Join the conversationComment
Share
Comments
Results
Contribute to the conversation