Allow me to fall into Cassandra syndrome (without any gender over- or undertones) for a moment:
For a backend NOT using delete/insert for update I'd guess the same as Kevin.
But STILL undefined area between ears suggests it is a bad idea.

Reasons:
SQL is set oriented, in most dialects you have to do some contortions to aquire behaviour similar to Order(Recno()) - and the dialects offering it are on the lower end of the spectrum like SqLite. High end DB might implement own data storage access mechanism not following traditional file system concepts.
Even if SSD nowadays is prominent, perhaps sometime in the future SQL server might change access mode and do that only for HD storage, making your app brittle.
Yes, IDentity as PK is more "sealed off" from tampering in SQL server compared to vfp - but you take great care to avert hackers compiling into C++ - then IMO it makes no sense at all to depend on DBA not to take server offline for a few minutes and execute some scripts unsealing column from PK role, run alter table to add more columns, re-sequence with or without fudging FK relations (which might thought about under Cui Bono POV) and reapply Identity PK role.

In your OP you mentioned perf benefits of table buffering, which I also use, but not with RV but CA.
Your alternative for more client side cursors does not sound THAT bad, but with CA I'd try different strategy: In the hook before_tableupdate (under hypothesis table lock in backend is established) I'd walk the cursor several times, each time scanning for a particular filter plus GetNextModified(), then massage record with SetFldState() after Upsert based on GetFldState() to get the upsert behavior and order you need.

If that one works, you can optimize further by building bulk inserts like Kevin mentioned (PK/FK problems to be resolved if there are relations client side), if connection between client and server is markedly slower than between memory and storage of both and TCP/IP lag adds a lot to single insert or update statements.
I know you built on RV, but perhaps move to CA might offer more choices and be automated reading out RV structures, programs and cursors ?

Shedding Cassandra syndrome...
thomas

>>> But with a clustered index, I'd be almost stunned if they didn't come back in order.
>
>Thanks Kevin. Main concern involved the transaction around a table-buffered update in VFP, with possibility of rows saved into freed-up space rather than appended to the end. I agree it would make sense for Identity to be assigned in sequence, if that's what the transaction definitely does- and I agree it seems to at least in 2021....