I do a couple different things. They kind of depend on the client and my
energy (sadly enough). One is I use ColdFusion to create two random numbers
and then I multiply them together. This might sound like it comes from out
of left field but it works extremely well! It's very fast and very
manageable. Just be sure to have a large enough range and change it for
different clients.
The second is the max_id type of thing that everyone else has been talking
about. But, as you've heard there are pros and cons to this.
Some people have talked about stored procedures but I've always thought that
a trigger for something like this would be better. Anyway, on that front
someone like Dave Watts or Bob Silverberg (sp?) would be the ones to
contact -- those guys know SQL inside out!
HTH,
Derek
----- Original Message -----
From: "Brian Scott Barnett" <[EMAIL PROTECTED]>
To: "Fusebox" <[EMAIL PROTECTED]>
Sent: Thursday, May 31, 2001 12:34 PM
Subject: Re: max_id or autonumber?
> 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
