Re: Sequences?

 What happens when someone has the bright idea of saving time by performing direct loads?

damorgan wrote:

> I disagree with your premise. What most developers do when there are multiple sources of
> import is write a before-insert trigger. That way there is no way for the sequence to
> not be used appropriately. Now I'll grant it is a few more keystrokes than 29 ... but
> then again it looks a lot better on your resume too so you get a payback.
>
> PS: There is no perfect database just as there is no other perfect tool. I don't care
> how wonderful a hammer is ... you can't use it to unscrew a bolt.
>
> Daniel Morgan
>
> Tony Berrington wrote:
>
> > Daniel,
> > I don't think the point is the difficulty of creating the sequence in the
> > first place, or in using it.
> >
> > The big problem arises when you have several potential sources of inserts/updates to
> > your data. You cannot guarantee that they will all use the sequence (and some may
> > actually not be able to).
> >
> > If you are using it for something as important as key generation, then you have a
> > very significant issue.
> >
> > By the way, I am an Oracle fan myself. It's a very good database, but not a perfect
> > one.
> >
> > Tony Berrington
> >
> > damorgan wrote:
> >
> > > That you haven't needed the capabilities provided by Oracle's sequences is fine.
> > > Some people, many people perhaps, don't. But some of us do. And for us ...
> > > autonumbering would be useless. So are you saying you'd rather have something
> > > that limits you to only one possible usage ... or would you rather have
> > > something robust, flexible, and scalable?
> > >
> > > But I must state that I find the amount of whining over this to be amazing. Is
> > > creating and using a SEQUENCE more difficult than creating an autonumbered
> > > column? Lets see:
> > >
> > > SQL> CREATE SEQUENCE s;
> > >
> > > The total characters typed was 18 including spaces and the semicolon. that
> > > created the sequence. I'm a bit sore but I'll continue.
> > >
> > > Now I'll add the sequence number to the insert statement for the table:
> > >
> > > s.NEXTVAL,
> > >
> > > Well that took a total of 10 keystrokes including the comma at the end. Probably
> > > 11 if you hit the space bar before continuing with the rest of the insert
> > > statement.
> > >
> > > A total of 29 keystrokes.
> > >
> > > Damn I guess you are correct ... my fingers are hurting and I'm developing some
> > > kind of syndrome that is going to require extensive use of pain medication,
> > > antiinflammatory steroids, and physical therapy.
> > >
> > > Daniel Morgan
> > >
> > > Heinz Kiosk wrote:
> > >
> > > > > There is no relationship between MS Access's autonumbering and a SEQUENCE.
> > > > The
> > > > > autonumering is part of the table and is only capable of sequential
> > > > numbering.
> > > > > It has no flexibility, no programmability, and is strictly tied to a
> > > > single
> > > > > table. All it is is the following code built in and hidden from the end
> > > > user.
> > > > >
> > > > > SELECT MAX(numbering_field)
> > > > > INTO next_number
> > > > > FROM xyz;
> > > > >
> > > > > INSERT INTO xyz
> > > > > (numbering_field + 1, other_field1, other_field2)
> > > > > VALUES
> > > > > (next_number, someval1, someval2);
> > > > >
> > > > > Daniel Morgan
> > > >
> > > > Methinks Daniel doth protest too much ;). "All Autonumber/Identity is" is
> > > > something exteremely useful that answers 99% of needs for this kind of
> > > > thing. I've never wanted numbers that cut across tables and I've never
> > > > wanted anything other than incremental numbering in 20 years of db schema
> > > > design. I agree "create sequence" is more flexible than MS SQL identity or
> > > > MS Access autonumber or DB2 identity or Sybase....(long boring list of rival
> > > > technologies snipped); but sequences are also a pain in the arse when all
> > > > you want is a system generated identity (as supplied in easier form by every
> > > > other db platform I've ever seen). Also are you seriously suggesting that
> > > > the above is the algorithm that any db actually uses? I think not.
> > > > (particularly as you wrote it wrong, SQL syntax error. Also potential
> > > > problems with above algorithm with transactions and synchronicity. Never
> > > > mind)
> > > >
> > > > Regards
Received on Wed Feb 20 2002 - 05:01:49 CST