Re: automatically calculating ID field on INSERT

Hi William,

William Boyle wrote:
>
> Joe Celko wrote:
> >
> > >> I think that does not solve the OP's problem since AFAIK the select
> > will result in NULL when the table is empty. <<
> >
> > You are right:
> >
> > >> I'm not sure whether such a function exists in standard SQL and what
> > it's called, but I guess that every DMBS has one. <<
> >
> > Here is the SQL-92 version:
> >
> > INSERT INTO MyTable (id, a, b, c, ...)
> > VALUES (COALESCE(SELECT MAX(id) FROM MyTable) + 1, 1),
> > new_a, new_b, new_c, ..);
> >
> > --CELKO--
> > Joe Celko, SQL and Database Consultant
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
> Unfortunately, this makes the assumption that there has never been a
> higher id in the table than currently exists, which under some
> situations could be a mistake. Oracle has sequence numbers which
> automatically increment, although this feature is not necessarily
> available on other DBMS products. Without such a facility, this is a
> difficult problem to solve with a single SQL statement. If you can
> execute it within a transactional block of statements, then an
> alternative is to use an ID table which one updates (increment the id
> value) and extracts the new value before inserting the new row into
> MyTable. IE:
>
> IdTable has 2 columns: tablename (char or varchar) and id (number or
> int)
>
> update IdTable set id = id + 1 where tablename = 'MyTable';
> insert into MyTable (id, a, b, c, ...)
> values ((select id from IdTable where tablename='MyTable'),
> new_a, new_b, newc, ...);
> commit;
>
> William Boyle

Though this approach will work in a lot of situations, is main drawback is that in the end it lacks scalability. For every insert being done the IdTable will be locked, and other transactions will have to wait till the transaction commits. For small to medium systems this will be fine, but for real large systems this will become a bottleneck.

The logical question now is: what 's the alternative. Well, not an easy one to construct in SQL. I 'm afraid that would be something distributed yet synchronized. On the other hand, it doesn't hurt to know the limitations of a given approach.

Regards,

Ruud

-- 
--------------------------------------------------------------------------------------
Ruud de Koter                    HP OpenView Software Business Unit
Senior Software Engineer         IT Service Management Operation
Telephone: +31 (20) 514 15 89    Van Diemenstraat 200  
Telefax  : +31 (20) 514 15 90    PO Box 831
Telnet   : 547 - 1589            1000 AV  Amsterdam, the Netherlands
Email    : ruud_dekoter_at_hp.com

internet: http://www.openview.hp.com/itsm
          http://www.openview.hp.com/assetview
intranet: http://ovweb.bbn.hp.com/itservicemanager
--------------------------------------------------------------------------------------