Derek,
Can you recommend a good alternative to the identity seed feature in SQL 7
for coming up with Unique ID fields?
Brian
----- Original Message -----
From: "Derek Hamilton" <[EMAIL PROTECTED]>
To: "Fusebox" <[EMAIL PROTECTED]>
Sent: Thursday, May 31, 2001 10:23 AM
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
