bottom line is that it is a pain in the keester to make a DB portable with identity fields as a PK
If you visit http://www.fusebox.org/index.cfm?fuseaction=learn.gallery there is a nice little custom tag for using max(ID) instead of identity also. Doug ----- Original Message ----- From: "Gyrus" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, November 26, 2001 6:10 PM Subject: Re: SELECT MAX(ID), Access & SQL Server - confused! > Thanks for all the feedback everyone - but I've still > some lingering questions... > > > In SQL Server, it's called an IDENTITY column. It's basically the same > > thing. Most database systems have some way to create a surrogate key - a > key > > which is created by the database, rather than drawn from the actual data. > > A developer who handled converting my Access DB to > SQL Server for a client told me I had to rewrite all my > INSERT queries to use SELECT MAX(ID), because there > was no equivalent to AUTONUMBER in SQL Server. Eh? > > My problem is, I want to create an Access-based app > template where the SQL code is as portable as possible > between DB systems - specifically, Access and SQL > Server. > > Why not carry on using AUTONUMBER, and convert > that field to IDENTITY if upsizing? Why bother having a > slightly risky 2-step transaction (or slightly performance- > hurting transaction if isolation="serializable") for Access, > only to have to switch all this to a stored procedure or > trigger when upsizing to SQL Server? > > Why do Fusebox advise using the MAX_ID tag for and the > 2-step INSERT for "portability" reasons - is this only when > converting to non-MS stuff like Oracle or Sybase? Steve's > documentation for the tag says: > > "Because autonumber datatypes are not easily transferable > from database to database." > > Does this count for Access > SQL Server (i.e. AUTONUMBER > > IDENTITY)? I've not upsized Access > SQL Server before. > Maybe I should give it a go before I finalise this template > code, but I thought I'd check with some people who have > probably struggled with this a while ago ;) > > - 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
