Re: SQL Sequence use cases

[Charles Wilt <charles.wilt@xxxxxxxxx>]

David,

While I understand where you are coming from; however a quick look at
the doc for Oracle shows that Oracle's sequence behaves the same way.
Note MS SQL Server doesn't offer sequences.

I can't say as I've actually used a sequence before, though I've
considered them and honestly I'm alright with the default behavior.

Consider the following: if they defaulted to ORDER and NO CACHE as
you'd prefer, what are you really gaining?  If you've got numerous
jobs using the sequence the only thing you're getting is a value that
corresponds to the order the rows were inserted.  If I'm interested in
that, I'll add a timestamp column to the tables involved.  Even with
ORDER and NO CACHE you're not guarenteeing that the final table(s)
will have no gaps since an insert could have been rolled back thus
wasting a value.

Note that Identity columns also default to CACHE.

IMHO, I think you're reading more into the name than you should.

Charles

On Fri, Nov 27, 2009 at 12:58 PM, David Gibbs <david@xxxxxxxxxxxx> wrote:
> On 11/25/2009 2:05 PM, Charles Wilt wrote:
> > There's a trade off between "sequential-ness" and performance,
> > particularly with multiple jobs using the same sequence.
>
> Yeah, I thought about that too.
>
> But I've always been of the opinion that an application should be designed first for functionality and then later for performance.
>
> > If all you really need is a surrogate key shared between tables, then
> > the defaults of NO ORDER and CACHE 20 provide the best performance.
>
> I guess my biggest problem with the implementation is that the name 'sequence' implies a shared _sequential_ numeric value ... it's actually anything but that (at least by default).  I think it would be more accurate to describe the concept as providing a _unique_ numeric value.
>
> david
>
> --
> IBM i on Power Systems - For when you can't afford to be out of business
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.