The technique/CF Code I posted earlier (where you store the current MaxID
in a table with one row) was taken from a Microsoft recommendation/article
a few years ago (during SQL 6, I believe).
best, paul
At 12:34 PM 5/31/01 -0700, you wrote:
>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
