|Knowledge Article Number||000006447|
Autonumbers are always incremental but from time to time will not be sequential.
Technical Explanation - SF will never attempt to make autonumbers consecutive (or Sequential). To do so, even in the simple case of saving one single object one-at-a-time, no two create operations can be executed in parallel.
For Example: The first create operation gets an autonumber value of 15 and attempts to save to the db and it fails. If the second creation operation is being executed in parallel and has started (attempting to save the autonumber value of 16) but cannot complete because 15 would be left out. As this situation generally involves more than just 2 records, one can see that the situation becomes much more complicated when we have bulk saves of multiple objects via the API where some of the rows may succeed and some may fail (due to apex triggers, for example). And when none of these complex operations can execute in parallel, the throughput will suffer and system resources will be tied up.
Therefore at this time, SF has chosen to maintain performance in our code than to guarantee sequential order of autonumber fields.
Workaround may be labor intensive and cannot guarantee exacting results as Custom Objects often reference other related records which may or may not be deleted based on this process (PROCEED WITH EXTREME CARE) when deleting any records from SF.
For Example - Custom Object has 300 records with the first 200 being numbered from 1-200 and the Last 100 records shows autonumbering as 251-350.
1. Export all records following "Gap" in Autonumber field (records 251-350) and delete them from SF (ALWAYS PERFORM BACKUP before any deletion of records).
2. Reset "Autonumber field to start with first unused value - 201 (OR 1 greater than the last present record in SF - 200 +1 = 201)
3. Insert deleted records so that on create of these new records, they will be numbered from 201-300.
***Again, we do not guarantee sequential autonumbers so this process may need to be repeated a number of times until the result is reached for all records.