Both MySQL and MS SQL Server Use @@Identity To Report Latest Auto-Incrementing Value

Posted December 9, 2008 at 10:14 AM by Ben Nadel

Tags: SQL

Reader Comments

Hi Ben,

I've always shied away from @@identity in MS SQL, as it's not limited to scope (ie it will return the identity generated by any triggers rather than the one generated in the table).

I've found that scope_identity() is much more reliable (it's limited to the scope), but I don't know if there's a MySQL equivalent.

@Francois,

I've seen people say this about @@Identity before, but I guess I don't really understand it. I don't think I've ever had a problem with it before. Are you saying that it will conflict with another user? Or with something else on the same table?

might want to reconsider that. The latest version of MySQL is getting a lot of slack because of the many show stopper bugs it contains.

http://www.eweek.com/c/a/Database/MySQL-Founder-Urges-Cautious-Approach-to-MySQL-51/

http://blogs.computerworld.com/mysql_5_1_released_with_crashing_bugs

http://episteme.arstechnica.com/eve/forums/a/tpc/f/6330927813/m/377006385931/p/1

on the side note: you should NEVER be using @@IDENTITY to get the last inserted identity from a table. this has been shouted down from the havens numerous times. @@IDENTITY will return the last inserted identity from ANY table of TRIGGER within the transaction session. you MUST use SCOPE_IDENTITY(). even IDENT_CURRENT is dangerous from the BOL about the dangers of using anything but SCOPE_IDENTITY():

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.

@Ben,

If you have a trigger on the table, and it also generates a new id (let's say it logs a transaction somewhere else, on a different or the same table), that is the id that will be returned by @@identity, rather than the one on the original insert.

details here (with example): http://www.databasejournal.com/features/mssql/article.php/10894_3307541_2/Getting-the-Wrong-Identity-in-Microsoft-SQL-Server-identity-Columns.htm

@Francois,

Ahh, I gotcha. I am not a fan of triggers (at this moment). I'm one of those silly people who thinks that database triggering is "business logic" and should be in the business layer ;) Still learning.

@Tony,

Can you define "within the transaction session". I do not know how this translates to a CFQuery tag?

@Ben,

It may be silly, but it's really handy when all you need is a transaction log ;). Why have CF do the work when the DB engine is made for this kind of stuff.

As for your question for Tony, your session is limited to your cfquery tag or, if you're using cftransaction, to your transaction block.

hth

"within the transaction session" all sql statements that get executed within a session. example would be doing an insert statement and then having that insert statement fire an insert trigger on the table. the transaction session contains two statement: one for the insert statement and one for the insert trigger that was fired.

@Francois,

Let's not worry about that philosophical discussion ;)

So, if I use a CFQuery tag, then my @@Identity is confined to that given CFQuery tag? Seems like that should never cause a problem in my use-cases.

@Ben,

OK then, we won't. As long as you know the risks, you're a grown man and can make your own decisions :P.

@Tony,

So, from what it seems, assuming I'm the kind of cool dude who doesn't use triggers and I refer to @@Identity directly after an INSERT, then I should never run into a problem, correct?

@Francois,

It seems that given my programming techniques, there is no risk.

@Ben,

Indeed.

@Francois,

Woot woot :)

I've stopped using platform-specific methods like @@identity and LAST_INSERT_ID or even worrying about the auto-incremented key because of all the potential portability problems and other pitfalls. I like this guy's take on it and his simple solution:

http://mysecretbase.com/get_the_last_id.cfm

I have been using the CF features to get the last insert id:

http://www.forta.com/blog/index.cfm/2007/7/6/ColdFusion-8-Can-Return-Identity-Values

This is one of the reasons why I use UUID's rather than auto incrementing fields. With a UUID, you can create the variable in CF and then insert it into the database, so you only have an insert statement rather than an insert followed by a select statement wrapped in a transaction.

@Andrew

AMEN on that brother, however in MSSQL it's better to use a GUID (uniqueidentifier).

I've always been a little hesitant of the UUID angle. Maybe cause I simply learned the auto-incrementing strategy first. But, more than anything, I think that UUIDs create nasty URLs. I like that I can easily cut / paste / and guess other URLs using simple IDs.

I suppose from a user's point of view, however, that it not relevant.

I also employ the process outlined in http://mysecretbase.com/get_the_last_id.cfm. I create a unique identifier (say a UUID), insert that along with the record, and then perform a select to pull the ID that corresponds to that UUID. That is, I still have an integer auto incrementing primary key in my table, but also a secondary UUID key (field called "uuid_key"). I obviously build a unique index against that UUID key field. I was somewhat hesitant to employ this solution as it introduces additional data to my tables, but after having built an entire application this way, I think that it is a good, if not elegant solution.

-Brian

These are all great arguments which I have read up on before, but as Ben stated earlier, I was also "brought up" on auto-increment ID keys so I guess it's just what I'm used to.

I've also used @@identity for a long time now in many applications and given that I have never had a need for triggers (or at least thought I haven't), it has worked great for me. I used to employ MySQL's native last_insert_id(), but started using @@identity to make my SQL code more portable between MSSQL and MySQL which are both used where I work. That's not to say I think triggers are bad, I just haven't found a need for them (and would obviously opt for another solution if I ever employed them).

@Ben I apologize if I have steered you wrong, but despite that, the @@identity solution has worked well for me. Best practice? It looks that might be a topic for another discussion. :)

@Anthony,

You did NOT steer me wrong; you steered me just right. I love @@Identity; it's never steered me wrong. Now that I know it's cross-platform on MS SQL and MySQL, it makes me want to use it more :)

So . . . when is it better to use UUID rather than auto-increments?

@Lola

my professional opinion is that you should ALWAYS use uuids. Now before everyone in auto-increment world goes nuts, i want you to think about how you would solve this problem if you use auto-increments in your database:

How would you go about combining 17 separate databases into one database?

Yes, this is what I'm doing right now and believe me that if I was using and continuing to use auto-incrementing keys, this would be almost impossible to do. Since I'm using uuids (guids), it's a breeze.

@Tony,

That task is going to suck no matter what :) In the past when I have to transform data, I usually create completely new IDs and then add an "_id" column to store the previous ID so that I can compare data using either IDs.

Unless I need to for some SEO reason, I don't worry about maintaining the previous IDs.

@Tony,

+1 on having to combine either DBs or Tables (and, yes, this happens in the real world). UUID / GUID also allow me to create OO objects with IDs even prior to persistence in the database. UUID / GUID ftw.

@Lola,
If you read the link that I referenced above and also looked at Brian Hendel's comment above (who also referenced the same link), that technique uses BOTH auto-increment and UUID. I won't go into explaining it in detail (which is why I provided the link). But basically you can have the best of both worlds: the simple URLs that Ben was talking about and the ability to get that last inserted ID without having to deal with race conditions, choke points in your code resulting from cflocks or cftransactions, or platform-specific solutions like @@identity or LAST_INSERT_ID(). (Yes, I know that @@identity now works on both MySQL and MSSQL, but what if my client wants to run my app on PostgreSQL?)

@Tony,

I think that approach is valid; seems like a good compromise for people still wanting the UUID approach.

@Ben - I'm running something similar to this. I'm starting to use mysql, in mssql, I'm used to running 2 queries within 1 cfquery tag. However, this seems to not work properly with mysql in ACFML10... Do you know if this is the case?

thanks -Al

@Al,

If you're having a problem running two queries in one CFQuery tag, it sounds like your datasource configuration is lacking the:

allowMultiQueries=true

... setting.

http://www.bennadel.com/blog/1542-MySQL-3-4-com-mysql-jdbc-Driver-And-allowMultiQueries-true.htm

That said, if you can run two queries, but you're having trouble selecting the @@Identity, then you probably need to store it in an intermediary variable.

Sometimes, I'll have something like this:

-------------
INSERT INTO ... query ;

SET @id = @@Identity ;

... some other query / queries ...

SELECT ( @id ) AS id ;
-------------

This way, I have that intermediary @id variable stored before I run any other queries.

Post A Comment

Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>
	Remember my information
	Subscribe to comments
	Send me a copy of this comment