Art S. Kagel wrote:
>
> sujata_soman_at_omm-la2-infotech-001@internet.omm.com wrote:
>
> > Hi all,
> > Some questions on how Informix handles the "Serial(n)" data type.
>
> > 1. How is the "next serial number" calculated ? why do I sometimes see different
> > behaviour(s), for eg, if we have 5 rows, numbered 1 thru 5, and I delete #5, and
> > insert a new row :- On some occasions, the new serial number is #5, and on
> > others, it is #6 - Why ?
>
> I've never seen this the next record added should always be 6 unless
> the record allocated serial number 5 was rolled back and no other row
> was added to the table between the rollback and the new insert.
It should always be 6, never 5. Even if the insertion of row 5 was
rolled
back, the "next serial value" is incremented and this is never rolled
back.
> > 4. Are the "holes" in a serial type column ever filled/re-used ??
>
> Not by the automatically generated values, though you can insert rows
> with explicit serial numbers and use these 'holes'.
Well, the "holes" would be re-used if your serial value ever reached the
2-billion-and-whatever value at which it wraps back to 1 (or if you
manually inserted 2,xxx,xxx,xxx into the serial value), but you probably
don't want to do that, because it will also re-use (or attempt to) the
"non-holes", and you will end up with duplicate values, or a bunch of
errors for trying to insert duplicate values in a unique field.
--
June
---- June Tong Informix Software ----
---- Senior Consultant (650) 926-6140 ----
---- International Support junet@informix.com ----
---- Location-du-jour: Menlo Park ----