NumberSequence vs. AutoIncrement for Log (and other) Tables

[pri-kise]

Since BC24 it's easier to deal with Number Sequences.
In a recent yammer thread there was a question on how to reset a table with AutoIncrement in BC SaaS.
The discussion came to the conclusion that sometimes it's usefule to use NumberSequence instead of an Primary Key with AutoIncrement.
https://www.yammer.com/dynamicsnavdev/threads/2959889852440576

This pattern can already check out in table "Integration Log Entry".
https://github.com/StefanMaron/MSDyn365BC.Code.History/blob/master/BaseApp/Source/Base%20Application/CRM/Interaction/InteractionLogEntry.Table.al

Before we can add this as a pattern, we have to discuss when this is a good pattern and when to use this pattern.

[NKarolak]

I am rather asking myself: When should AutoIncrement be used at all? We have absolutely no control over it and cannot reset it. At least for my dev container, where I am the only user filling a custom log table, it creates huge number gaps (of e.g. 90) that I cannot explain. (Can you? I am not aware of aborted transactions. Edit: Silly me - it must be the automated tests when run in the client) And once the highest possible (Big)Integer Entry No. will be reached, the table needs to be replaced with a new one.

Back to the new pattern - as it took me a bit too long to understand what happened here:

BC inserts the Interaction Log Entry the following way:

                InteractionLogEntry.Init();
                InteractionLogEntry."Entry No." := SequenceNoMgt.GetNextSeqNo(Database::"Interaction Log Entry");
                ...
                InteractionLogEntry.InsertRecord();

Usually, InteractionLogEntry.Insert() here would have been sufficient. But since other apps can theoretically insert entries using another logic and thus could have used the same Entry No. that BC is now about to use, Insert() could now fail. This is why it was solved using InsertRecord() instead:

    procedure InsertRecord()
    var
        SequenceNoMgt: Codeunit "Sequence No. Mgt.";
    begin
        if not Insert() then begin
            SequenceNoMgt.RebaseSeqNo(DATABASE::"Interaction Log Entry");
            "Entry No." := SequenceNoMgt.GetNextSeqNo(DATABASE::"Interaction Log Entry");
            Insert();
        end;
    end;

Hence: only in case the Insert fails, rebase the number sequence, choose another Entry No., then insert again.

I am just wondering whether another approach would not have been better in terms of bulked inserts:

    procedure InsertRecord()
    var
        SequenceNoMgt: Codeunit "Sequence No. Mgt.";
    begin
        if EntryNoAlreadyExistsOrIsEmpty() then begin
            if "Entry No." > 0 then
               SequenceNoMgt.RebaseSeqNo(DATABASE::"Interaction Log Entry");
            "Entry No." := SequenceNoMgt.GetNextSeqNo(DATABASE::"Interaction Log Entry");            
        end;
        Insert();
    end;

As you can see, I am wondering also about why empty Entry Nos. were not handled as well.
We could then insert the entry like we would have with AutoIncrement:

                InteractionLogEntry.Init();
                InteractionLogEntry."Entry No." := 0;
                ...
                InteractionLogEntry.InsertRecord();

It would be nice if the new pattern included also instructions how to move an AppSource app table from an AutoIncrement field to a normal one.

[pri-kise]

I'm not a big fan of this InsertRecord method.
Shouldn't it be possible to check for an empty "Entry No." in the OnInsert Trigger instead

[NKarolak]

Sure, everything could also be put into the OnInsert trigger instead (something that I've also wondered about).
Then the required insert pattern would look like this:

                InteractionLogEntry.Init();
                InteractionLogEntry."Entry No." := SequenceNoMgt.GetNextSeqNo(Database::"Interaction Log Entry");
                ...
                InteractionLogEntry.Insert(true);

and

    trigger OnInsert()
    var
        SequenceNoMgt: Codeunit "Sequence No. Mgt.";
    begin
        if EntryNoAlreadyExistsOrIsEmpty() then begin
            if "Entry No." > 0 then
               SequenceNoMgt.RebaseSeqNo(DATABASE::"Interaction Log Entry");
            "Entry No." := SequenceNoMgt.GetNextSeqNo(DATABASE::"Interaction Log Entry");            
        end;   
    end;

[miljance]

That looks much better and more usable.