Retrieving newly inserted primary keys in SQL Server 2000

This tip originally appeared on SearchWin2000.com, a sister site of SearchCIO-Midmarket.com.

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.