Re: Sequences?

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 Tue Feb 19 2002 - 10:54:24 CST