Re: INFORMIX & DATA TYPE SERIAL: Questions

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 ----