> Am I right in thinking SQL Server doesn't have the > 'Autonumber' data type? Why not? Is there a grave > disadvantage to it unless you're doing kid's stuff in > Access?
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. > Is it cool to use the SELECT MAX(ID) with SQL Server > 2000 (i.e. do the 6.5/7 problems above not happen now)? You're better off taking advantage of the features of your database. Instead of doing two queries, you can have one interaction with the database in which you insert the new record and retrieve the new key. You can do this with a stored procedure or trigger. I didn't read the link you posted, but if I recall correctly, the problem to which you're referring is the possibility that you might get the wrong key due to row-level locking within transactions. If that's the case, that "problem" - it's not really a problem with the database, but rather one with the expectations within the application code - still exists, and you wouldn't want to use the SELECT MAX(ID) approach. > If I put <cftransaction isolation="serializable"> around > the SELECT MAX(ID) and INSERT queries (in whatever > order), are there any hidden dangers? Does this prevent > anything else from changing the table concerned until > the new ID is calculated and the nice new record is > INSERTed? You may unnecessarily hurt performance if you make your transactions serializable. There's no need for it in this case - just use a stored proc or a trigger. However, I'm pretty sure that, yes, it would prevent someone else from inserting a record until you've retrieved the key. > You can't use stored procedures or triggers on Access, > right? Access doesn't support ANSI SQL stored procedures, but you can create parameter queries in Access, and you can use CFSTOREDPROC to call them from within your CF application. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: (202) 797-5496 fax: (202) 797-5444 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
