Despite the large number of SOQL fragments that include "order by Id", my understanding is that "order by Id" does not guarantee that the records are returned in insertion order.
Correct. I go in to more details here, but the gist of things is that a Salesforce instance, called a "pod", is comprised of various servers, and each server requests a "chunk" of Ids to assign out. Thus, two or more users on two or more servers performing inserts at the same time may end up both assigning sequential Ids relative to themselves, but out of order chronologically. You can only guarantee that Id values are increasing for a single user in a single transaction, once you start talking multi-transactional, all bets are out the window.
(Is it only after deletion/emptying that results in out of order IDs or are there other circumstances? Does anyone have insight into why the IDs are not in order?)
Id values are never reused, even if a record was deleted, was part of a unit test, was rolled back because of partial database saves, or a fatal exception/error occurs that rolls back the entire transaction. Those Id values are lost forever.
Also CreatedDate appears to to have a granularity of 1 second. So while "order by CreatedDate, Id" will work most of the time, if two records are created within the same second where one ID is being reused, again the order cannot be guaranteed.
Correct. The milliseconds isn't a guaranteed order. Generally speaking, records are by default ordered by CreatedDate and Id. Query optimizations may also cause records to shuffle around in order within this one second granularity, but is generally stable from user to user.
Is there a solution that guarantees that records are returned in insertion order?
As stated in other answers, an Auto-Number field will always be assigned in approximately insertion order. However, I'd like to add that, because of race conditions when many inserts happen in close proximity chronologically, there may still be edge cases (e.g. timing differences on how long a trigger takes to execute).
In an example script where I create a before insert trigger that waits X seconds before continuing on, I was able to prove that auto-number fields can also be out of order, even if some transactions started earlier.
Visualforce.remoting.buffer = false;
Promise.all(
[8, 6, 4, 2].map(
(delay) =>
new Promise((resolve, reject) => {
setTimeout(()=>{!$RemoteAction.q9158.insertRecord}(delay, (record) => resolve(record)),(9-delay)*250);
})
)
).then((results) => console.log(JSON.stringify(results)))
Output:
| Id |
Start |
End |
Duration |
CreatedDate |
AutoNumber |
Delay |
| a0H1T00000MDSZWUA5 |
...52565 |
...60618 |
8053 |
...60000 |
A-0083 |
8 |
| a0H1T00000MDSZRUA5 |
...53205 |
...59343 |
6138 |
...59000 |
A-0082 |
6 |
| a0H1T00000MDSZMUA5 |
...53560 |
...57601 |
4041 |
...57000 |
A-0081 |
4 |
| a0H1T00000MDSZHUA5 |
...54052 |
...56087 |
2035 |
...56000 |
A-0080 |
2 |
As you can see, with a ~500 millsecond delay between each record, but the first taking the longest amount of time, It ends up getting a higher auto-number value and Id. Granted, this is a contrived example, but it just goes to demonstrate that the order may be considered inaccurate if you care about which records started first.
If you want sub-second precision, and want a reasonable guarantee that the insertion order is "strictly" preserved, write a trigger to assign DateTime.now().getTime() to a number field for each record at the earliest possible moment. This would give you far greater accuracy than sorting by Id, CreatedDate, SystemModStamp, etc, barring potentially millisecond discrepancies between each server in a pod. It's the only way to get a "perfectly" preserved order.
Some testing yielded results like this:
Created Note Id: 002A000000DmPjn on 1/8/2013 Created Note Id: 002A000000EFwjk on 1/15/2013 Created Note Id: 002A000000EBOym on 3/5/2013
that then led to the (reckless) assumption that the ids were being recycled.
– Keith C Mar 05 '13 at 12:10Deleted Note Id: 002A000000EBOym on 3/5/2013 Created Note Id: 002A000000EBOzV on 3/5/2013
just evidence that the ids are not in insertion order. I'll edit the question to avoid promoting the idea that the ids are recycled. My mistake.
If you can offer an explanation of why the ids are not in insertion order (rather than me making more guesses) that would be appreciated.
– Keith C Mar 05 '13 at 12:11The autonumber solution will work but seems like overkill for just ordering a list on vf page.
– Greg Grinberg Mar 07 '13 at 01:11ORDER BY CreatedDate DESC, Id DESCreally that unreliable? – Adrian Larson Nov 16 '16 at 23:16