Geeks With Blogs

News Dave's Mug View David Oliver's profile on LinkedIn Add to Technorati Favorites Blog Directory for Guildford, Surrey
Dave Oliver's Blog Enterprise Technology Thought Leadership in a FTSE 100

Arguably one of the most common things you will do in SQL Server stored proc's after writing SELECT, INSERT, UDPATE & DELETE statements is to retrieve the new record ID of the row of data that has just been inserted. For sometime now the most popular way to do this is @@IDENTITY, but @@IDENTITY does have a flaw and that is it will return the ID of the last record created anywhere in the database, so if another process is inserting a record somewhere else, you could receive that ID instead.

With SQL Server 2005 there is an answer for this and that is the SCOPE_IDENTITY() command that will return the ID of a insert statement that just occurred in context.

EDIT: Please read the comments on my posts, Paul and Toby have kindly taken the time out to comment and give a better and more accurate explanation than mine. Thanks for your feedback guys!

My reason for writing this is David Hayden has written a really good article on this and can be found here. As you can imagine this is an important change to communicate, so spread the word!

Also, I went to 'a certain well known hardware vendor' yesterday that resells both Oracle and SQL Server databases. They have a rule of thumb that databases over 1 terabyte and/or over 1,000,000 commands per minute went Oracle. As Oracle has made their licencing more complicate and basically more expensive, they have decided to give Oracle a bloody nose by not recommending them so much, specially with the advent of SQL Server 2005 which is hard of the heals of Oracle.

So my advice to Oracle is make your licencing less complicated and be more competitive! My advice to Microsoft is, don't do anything silly with your pricing model. The rule of thumb is keep it simple.

Posted on Friday, February 17, 2006 8:41 AM Main , Development Technologies | Back to top

Comments on this post: Database matters.

# re: Database matters.
Requesting Gravatar...
SCOPE_IDENTITY() is also in SQL 2000.
From BOL

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.

SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

For example, you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.

Assuming that both T1 and T2 have IDENTITY columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.

@@IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.

SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope. The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.

Left by Toby Henderson on Feb 17, 2006 9:08 AM

# re: Database matters.
Requesting Gravatar...
Just to be clear here, even though Toby has the details from the BOL here, I think its still wise to point out an error in your description in real world terms. While you really should not be using @@Identity in most cases (and should not have been doing this for 5 years now), it is not as bad as you are reporting either. It will NOT return the ID of the last record created "anywhere" in the database, nor will it return IDs inserted by another process. Instead its a much more subtle problem that only occurs if you have something like the class Insert Trigger that Toby notes -- something that inserts another record in the SAME session. So yes to be safe it is recommended to always use SCOPE_IDENTITY() unless you really have a reason to be using @@Identity -- and I can't think of a good reason.
Left by Paul Wilson on Feb 17, 2006 10:05 AM

# re: Database matters.
Requesting Gravatar...
Thanks for pointing out those issues to me guys.

Rather than change my post I've decided to repoint people to read the comments which you guys have taken the time to write.

Many Thanks for your feedback. Everyday is a School day.
Left by Dave Oliver on Feb 17, 2006 11:41 AM

# re: Database matters.
Requesting Gravatar...
It could be worse, you could've been using:

Select MAX(ixFoo) FROM foo;

for the last 5 years ;o).

Left by Carl Wright on Feb 17, 2006 1:34 PM

# re: Database matters.
Requesting Gravatar...
lol Carl!

Select MAX(ixFoo) FROM foo; !!!

The funny things is I've bet we have all seen this more than once!
Left by Dave Oliver on Feb 18, 2006 7:55 AM

Comments have been closed on this topic.
Copyright © Dave Oliver | Powered by: