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

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.

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

@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.

@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.

@Ben,

Indeed.

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 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. :)

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,

+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?)

@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