SQL Server Management Studio absolutely does drop your table and create a new one. Run a Profiler or XEvents trace to see the SQL that is being issued - you'll see the creation of a new table, copying of the data, dropping of the original table, renaming of the new table. This is why you have to disable the 'Prevent saving changes that require table re-creation' Table option. If you do not disable this option, you will not be able to save changes like reordering columns.
There is no magic ALTER TABLE statement for reordering fields. Changing the physical order of columns will require re-ordering everything physically at the page level. This is not a trivial operation and I'll venture a guess that this is why none of the commercial dbms that we support do this - it is probably safer/cleaner to recreate the entire table.
How about a compromise capability? Esri could consider supporting field order changes in file geodatabases. There is a good amount of work to design, review, adjust, iterate while coming up with a usable GDB schema for most business purposes I've experienced. This work is hampered and made frustrating by the software not supporting field reordering. Once a schema has been developed to satisfaction, it could then be promoted to an enterprise geodatabase, within which users could understand via useful documentation that there is no support for field reordering. Don't forget to make the GP tools that look like they support field reordering support the file geodatabase, too. e.g. Feature Class to Feature Class - Field Map.
I have to agree with Tim. I'd be happy with the compromise approach, and allow field adjustments for personal, file GDBs, Shapefiles, and other simple/single user data sources. I don't mean to rain on anyone's parade, but I don't see how allowing fields to be moved around would help create a stable database server.
There are a couple of issues with this. First, there are many applications that use the field order index (first field = 0, next, 1, etc.). I think published services go along this line of process as well (could be wrong though on this point). Anyways, what the user sees on the front end is not always what is happening under the hood. When one end of the system changes, it can break a lot of other things very quickly-and that can cause much more effort to fix.
I might be overstating the importance, but the level of effort to go through designing from a simpler/single use data sources gives me confidence that my enterprise data and application will not be compromised.
Thanks for sharing this perspective of why you need to reorder the fields in your organization. Your reason for needing to reorder the fields permanently is due to the layer ordering not persisting through to your published feature services.
This has been documented as a known issue and is being reviewed. Please contact support services if you would like to be linked to this defect. This is specific to feature services and does not occur with map services.
BUG-000090535 - Feature Service Fields List Order is not maintained on REST endpoint.
Over 6-year old, top voted (twice as many as the second most) idea and still ArcGIS doesn't have this ability. I don't know why I even bother anymore to look up if this functionality has been added. Oh yeah, it's because I keep needing to do this but end up having to basically build a completely new schema into which to import the same data. At least support this on the non-SDE feature classes. Who or what is standing in the way of this? Stop the sandbagging already.
Quite possibly it gets ignored because it may not be possible to do it in a FileGeodatabase due to the nature of this type of database. And ESRI seems to have a push to make people use FileGeodatabases instead of Personal. We can easily reorder fields in PersonalGeodatabases but that does not help File users.
While a pure reordering of fields in an existing layer may not be possible, it can be done if you look at it another way. Let the user select the order they wish the fields to be laid out. Create a new empty copy of the layer with that structure and then transfer the existing data to this new layer. Create any permissions, indexes, etc on this new layer as it exists on the original layer. Delete the original layer and rename the new one. This is basically what SQL Server does in the background when a user reorders fields on a table. As far as the user is concerned, they are just reordering fields. They do not need to know the detail of how much work is actually being performed in the background, it just happens.
I'm running sql server 2014... if I go in and reorder the fields in SMSS, will that blow up anything in ArcGIS? I'm also using editor tracking and versioning, and publishing services on those tables.
There's a great Add-In, X-Ray for ArcCatalog I just found that does just this. Along with some other great geodatabase management tools, it allows you to re-order fields for file gdb's or SDE. More importantly it honors table functionality such as attachments and cartographic representations, which can get dropped with other tools. Worth a look. Available in 10.1 or 10.2 and above.
ArcGIS should allow the ability to permanently reorder fields in a table
new comment by Michael Barker<https://community.esri.com/people/mbarker> View all comments on this idea<https://community.esri.com/ideas/1362?commentID=40798#comment-40798>
