Hmmmm this is a hotly debated item always... blame portability and purists mostly for the reoccurrence....
I would personally take the approach to: A. Tie everything into one big transaction block... meaning... fetch existing ID highmark... create a lock on the next place (if possible)... write the data out... check for the validity of such... unlock things... B. I would use the native autonumber and id highmark fetching mechanisms built into your flavor of database. C. I wouldn't use big long unique strings... numbers do fine, are much shorter to sort on and involve less computation... shorter is better... Remember, all this fetch and math and sort and locking eats up transactional time... This means slower response to the client and more overhead on the server... Just a point of issue here, the database engines all get locking, unique IDs, etc. even under high-load the database would shuffle things and give you the right space/prevent two users from getting the same unique ID... can anyone tell me when and with what database this didn't hold true? It is based on this I say let the database do the work it knows best how to do.... Tell me if you think this is wrong... On the portability note... when migrating you might want to roll your identities into the new database and a non-identity field / non autonumber to preserve existing data and relationships... after you stop the bleeding of migration, assess the ease to enable it or build a secondary/shielded ID or ID to ID relationship mechanism... Nothing like migrating a few million records and having the field values not preserved... just did that from a SQL Server over to MySQL... glad we wasted hours on multiple imports.. couldn't get the IDs to pickup where the last on left off and dropped a bunch of date values.... -paris lundis -----Original Message----- From: Gyrus [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 27, 2001 07:41 To: CF-Talk Subject: Re: SELECT MAX(ID), Access & SQL Server - confused! > > Why not carry on using AUTONUMBER, and convert > > that field to IDENTITY if upsizing? > > Ordinarily I'd say that was what you should do (see below) > The trouble comes in when you have to pull the number out again, You mean if you're doing SELECT MAX(ID) *after* an INSERT, to get the ID that's just been created, instead of creating a new ID *before* the INSERT for 'manual' numbering of the PK? Couldn't you use the loop/cftry/ cftransaction combo that Steve Nelson suggests for creating a new ID? Must say I'm totally baffled. I've still found no solid reason why people even bother concocting slick and wonderful 'workarounds' for the ID-creation 'problem'. Why not use the DB's autonumber/identity feature, and use the best method available (loop/try/transaction, or @@identity I guess) for grabbing a newly created ID? There's a lot of back-and-forth in this thread, some really useful info, but no overall idea of where we stand with this - pretty fundamental! - DB issue. Anyone who could sum it all up - or just post a link to such a thing :) - would get my gratitude for one! - Gyrus ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
