Just to be clear on 2 things 1) > And thanks to Tim Lucas who recently brought to light the ability to use > @@IDENTITY in mySQL v4+.
@@IDENTITY is NOT supported in mySQL. Tim got mistaken about which DB we were talking about. In mySQL you use Last_INSERT_ID(). (Use http://www.mysql.com/search/index.php?q=@@IDENTITY&from=%2Findex.html if you doubt me) 2) If you are using a DB with stored procs, it's a minute DB hit to do your insert and return the ID out of the Database after you have done it. To do the 2 queries out of ColdFusion will, by definition, be slowler, as the stored proc is already AT the DB. On a final note - If you want to generate your own ID so you 'know what it is' I would suggest still sticking to a numeric value. Something you generate with some year-month-day-second-millisecond values and some random fields. I used to do this as well, and it was a reliable solution. However, to hit the DB One more time on an INSERT to get an IDis not worth the overall performance degredation that a DB running joins on a UUID is going to cause. But it's all about trade offs in the long run, maybe it makes more sense in your apps. It kinda boils down to - how long is a piece of string? Mark ------------------------------------------------------------------ [EMAIL PROTECTED] ICQ: 3094740 Safe From Bees [ www.safefrombees.com ] Quoting Brett Payne-Rhodes <[EMAIL PROTECTED]>: > @@IDENTITY may solve the 'lock' issue but it is still another trip to > the database to actually retrieve it. Personally I don't have a problem > with doing it but let's be clear about what is and is not true. > > > Brett > B) > > Taco Fleur wrote: > > This article is 5 years old.. > > > > "The problem with these is that finding the PK value of a newly inserted > > record requires another trip to the server and the DB -- and a lock on the > > database file while the DB determines the last ID inserted." > > > > This is not true for MS SQL, you simple use @@IDENTITY or SCOPE_IDENTITY > > > > > > Taco Fleur > > Blog http://www.tacofleur.com/index/blog/ > > Methodology http://www.tacofleur.com/index/methodology/ > > > > Tell me and I will forget > > Show me and I will remember > > Teach me and I will learn > > > > > > > >>-----Original Message----- > >>From: [EMAIL PROTECTED] > >>[mailto:[EMAIL PROTECTED] On Behalf > >>Of Yorke Hinds > >>Sent: Thursday, 12 February 2004 9:57 AM > >>To: CFAussie Mailing List > >>Subject: [cfaussie] Re: Whats your Primary Key? > >> > >> > >>I am using SQL Server. > >> > >>In 1999 Hal Helms wrote a brief article on the subject - > > > > http://www.halhelms.com/index.cfm?fuseaction=newsletters.aug1999 > > > > Any comments regaring Hals Article? > > > > > > > > > > > > > > --- > > You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To > > unsubscribe send a blank email to [EMAIL PROTECTED] > > > > MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia > > http://www.mxdu.com/ + 24-25 February, 2004 > > > > > > --- > > You are currently subscribed to cfaussie as: [EMAIL PROTECTED] > > To unsubscribe send a blank email to > [EMAIL PROTECTED] > > > > MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia > > http://www.mxdu.com/ + 24-25 February, 2004 > > > > > -- > Brett Payne-Rhodes > Eaglehawk Computing > t: +61 (0)8 9371-0471 > f: +61 (0)8 9371-0470 > m: +61 (0)414 371 047 > e: [EMAIL PROTECTED] > w: www.ehc.net.au > > > > --- > You are currently subscribed to cfaussie as: [EMAIL PROTECTED] > To unsubscribe send a blank email to [EMAIL PROTECTED] > > MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia > http://www.mxdu.com/ + 24-25 February, 2004 > > > --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004
