> 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?
That's an indication that it's time to: a) buy your developer an introductory SQL Server book, or b) find a new developer, or c) learn how to do it yourself (fortunately, it isn't that hard) > 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? If you're only dealing with Access and SQL Server, the behavior of autonumber columns in Access is very similar to that of identity columns in SQL Server, so there's no reason not to use them. However, your original question was about ensuring that you get the appropriate key after inserting a record. Here's where your solutions might diverge - you might find yourself doing it one way with Access and another way with SQL Server, while still using the same autonumber or identity field with both. > 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." Yes, Access and SQL Server handle surrogate keys relatively similarly - so does Sybase. Oracle, on the other hand, uses sequences, which are defined a bit differently. In any case, the difficulty may lie when porting databases that already contain data from one platform to another - this can be a bit more difficult when using database-defined surrogate keys. And of course, even if the key fields perform the same way, as they do with Access and SQL Server, the code to take advantage of their performance may very well be platform-specific, as you've seen. So, I would guess that Fusebox recommends their approach because Fusebox values portability over performance. On the other hand, I personally generally value performance over portability, and so I'd recommend the platform-specific code approach, although it may require a bit more development. Neither choice is "right", in the sense that it's always the appropriate choice. That's up to you, given your situation. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: (202) 797-5496 fax: (202) 797-5444 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 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
