Re: Generating Sequence Numbers

In article <7sasgh$d6j$1_at_nnrp1.deja.com>,   markp7832_at_my-deja.com wrote:
> In article <7s921q$3ud$1_at_nnrp1.deja.com>,
> Kevin Mackie <mackster_at_my-deja.com> wrote:
> > Is there a non-platform specific way to generate sequence numbers
> using
> > only SQL?
> >
> Yes, however the reason most rdbms vendors provide a sequence
generator
> is because the only way to generate a sequence with just SQL is to use
> a table row column as a counter which then requires a 'select for
> update' type query to prevent multiple processes from reading the same
> value. This creates a lock wait situation where all users of the
> sequence have to wait for the prior transaction to complete with a
> commit before they can get a number. This is not acceptable for most
> applications.

Thanks - that answers the question I posted. Unfortunately, I didn't explain what I was looking for very well. ;-(

The real questions is:

I need a new column on a table which will act like ROWID, so each row has a unique integer (which is between 1 and the number of rows in the table).

This is easily done proceduraly, but I need a set based solution that can be implemented purely via SQL.

Any ideas?

Thanks,

Kevin

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Sep 22 1999 - 12:30:15 CDT