You know I have heard that statement more times then I can remember....but
no one can ever tell me what they suggest as an alternative to their town
technique/technology...do you know what the alternative is?
-----Original Message-----
From: Derek Hamilton [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 31, 2001 1:24 PM
To: Fusebox
Subject: Re: max_id or autonumber?
Another issue with the identity feature of SQL Server 7 (not sure about 2000
however) is that the identity is tracked by the internal system tables of
SQL Server. If your server ever crashes or whatever then those tables could
be reset and you would run into a lot of errors or duplicate keys because
the identity would use values that had previously been used. Microsoft
doesn't even recommend that you use the identity feature for a Unique ID
field because of this.
Derek
----- Original Message -----
From: "Marsh, Jeffrey B" <[EMAIL PROTECTED]>
To: "Fusebox" <[EMAIL PROTECTED]>
Sent: Thursday, May 31, 2001 9:11 AM
Subject: RE: max_id or autonumber?
> If SQL 7 is your database, use stored procedures. Stay away from in-line
SQL
> as much as possible. Let the database do the work it is supposed to do.
> Don't let the web server do work it doesn't need to do. Whether you use
> identity seeds or not, the store procedure can figure out the next key to
> use in a table. Using maxID and then doing an insert makes unnecessary
trips
> to the database. Make every trip to the database count.
>
> ---
> Jeffrey B. Marsh
> Professionals built the Titanic.
> Amateurs built the Ark.
>
> -----Original Message-----
> From: Ross Keatinge [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, May 30, 2001 4:43 PM
> To: Fusebox
> Subject: max_id or autonumber?
>
> Hi folks
>
> I know this is an old recurring discussion but I still can't find a
> good answer in the various CF mailing lists.
>
> I'm soon to start building a new web application with CF and XFB. I'm
> convinced that meaningless integer primary keys for db tables are the
> way to go (although that is another discussion in itself). Our database
> is MS SQL Server 7. I'm still tossing up the question of whether to use
> the db provided identity functionality or roll my own using something
> like Steve's cf_max_id tag. I think identity fields work fine in SQL
> Server 7 and they are certainly tempting. However, I like the feeling
> of 'freedom' from db dependent features so a normal int field with
> SELECT MAX(ID) is appealing to some extent. As Steve's docs say, moving
> to another db is a lot easier without autonumbers.
>
> The recommended use of cf_max_id results in something like this:
>
> <cftransaction>
> <cfquery name="GetMaxID">
> SELECT MAX(ID) FROM table
> </cfquery>
>
> <cfquery>
> INSERT INTO table
> (ID, Field1, Field2 etc)
> VALUES(#GetMaxID.Max_D#,'#Field1#', '#Field2#')
> </cfquery>
> </cftransaction>
>
> Is that really safe in a multi threaded environment? It doesn't look
> like it to me. As far as I know <cftransaction> doesn't stop me losing
> the CPU between the two queries. If I am interupted there, what would
> happen to a second page hitting the same code? Would it just hang at
> the cftransaction or what? What odbc isolation lock type is
> appropriate? I know cflock would fix it but that seems like a bad idea
> or is it really any worse?
>
> Any comments would be appreciated.
>
> Thanks
> Ross
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
