Subject Re: [firebird-support] Performance: Best strategy for non-PK and non-alphabetical sort order for rows?
Author Wolfgang Rohdewald
On Mittwoch, 23. Mai 2007, mlq97 wrote:
> as new rows get inserted or reorganised, the previous
> numbers will need to be readjusted and this is a very unsatisfactory
> option.

you could use bigint for the sequence number and
assign the rows in big steps like 1000000. That should give you
sufficient room for lots of manual adjustments.

Take the middle value of a gap between the two positions where
the new position should go between. If the gap is empty raise

exception nogap 'please reassign all numbers and try again'

meaning the user must execute a procedure which reassigns all
numbers in big steps. Since this should almost never happen,
it should be enough.

I have no example code, I wrote something like that many
years ago in BASIC (and sort of dbase tables)