Re: Best approach for a "gap-less" sequence

Hi,

On Sat, 12 Aug 2006, chris smith wrote:

> On 8/12/06, Jorge Godoy <jgodoy@gmail.com> wrote:
<snipp/>
>> Is there a better way to guarantee that there will be no gaps in my
>> sequence
>> if something goes wrong with my transaction?
>
> Why does it matter?
>
> I assume there is a reason you need it like this..

For example german tax law requires invoices to be numbered
sequentially without gaps.  This is supposed to make it harder
to cheat on VAT.

You cannot just drop an invoice as that would leave a gap.  Tax
inspectors will search for gaps and query to whatever invoice
is missing from records.

I could not care less about gaps in surrogate keys but this
kind of stuff is an external requirement.

Theres propably not much choice on how to implement something
like this but to just store the last assigned number in some row.

I would at least try to assign multiple such numbers in batches
to mimize contention on the row you store the counter in.

Greetings
Christian

--
Christian Kratzer                       ck@cksoft.de
CK Software GmbH                        http://www.cksoft.de/
Phone: +49 7452 889 135                 Fax: +49 7452 889 136