ArcGIS should allow the ability to permanently reorder fields in a table
No it's not a limitation in the DB. We actually do this on average once every other week at least due to front end application design changes/needs or a new field being added that users want showing up in a specific order somewhere along the architecture chain.
It's actually really straight forward in SSMS and is done by right-clicking on the table in the drop down that appears click on "Design" and then in the design view it's literally drag and drop to re-arrange fields where you want them. Click save, refresh your Arc products. Below is an example of the design screen and its the small boxes to the left of the field names that when selected an arrow appears inside the box and you can then drag and drop.
It is much less intuitive than Desktop, so since Pro is constantly being touted as a ground up redesign with a focus on fixing Desktop's non-intuitive property access screens, this is one instance where Pro is creating a much less intuitive interface for the field rearrangement aspect at least. In Desktop rearranging the fields in the table view is accessing the field arrangement properties of the field screen, which is the most intuitive in my view. The disconnect is not intuitive, especially when it lets me rearrange fields in the table view but never pointed me to the Fields property screen to make the field arrangement permanent in the Project or warned me that my field reordering would disappear. Now that I know, I regard that feature in the table view screen as nothing but a confusing waste of time and a great way to make sure users of Desktop won't understand how to transition to Pro.
I doubt I ever would have figured this out on my own without coming to the forum. Here is a screen shot of what comes up in the help when I launched it from ArcGIS Pro and searched for "field order arcgis pro" Nothing about any Fields property screen is mentioned in any of the topics returned within the first 3 screens of results. I don't know if it is any of the screens, but I would have given up after the 4th screen when topics like "Web scene layer - ArcGIS Pro | ArcGIS for Desktop" came up as a leading topic.
.
I'm by far a Sql expert, but you can disable the option in SSMS to prevent saving changes that require table re-creation, as highlighted in the screenshot below. If this is disabled, you can re-arrange fields within a table without having to drop and re-create the table. Esri would probably not support or condone such an action, but as the many previous posts have mentioned, it's a nice-ity to have the field arranged as desired when adding to ArcMap/ArcGIS Pro...and this is something I personally will do if I have added a field to a feature class/table that I'd like shown elsewhere in the table order.
