Does adding a field to FGDB cause a table rewrite? Could rows be reordered? What other operations might reorder rows?

AZendel

Howdy folks. I'm not using the FGDB API at all. But I think is the best community/forum to ask because it's a question about low-level FGDB functionality and manipulation. I'm using 3rd party software that's built on top of Pro. It involves a number of tables in a file geodatabase. And it's critical that rows remain in their original order because re-ordering rows caused catastrophic results in their software. I think they're looping through multiple tables that they expect to be in a particular order rather than using proper database joins on identifier fields.

I would like to add fields to their FGDB tables "behind the scenes" in the catalog pane in Pro. I'm aware that schema changes will cause many RDBM systems to fully re-write the table. Is that the case for FGDBs? If it does require a re-write, will the new table have the rows sorted in the same order?

In a related matter, are there other operations on FGDBs that may alter the order of the rows?

Thanks for any insight that you can provide.

JoshuaBixby

I thought I have stated the following somewhere on Esri Community, but I can't find it right now, so I will excerpt one of my response on GIS StackExchange:  arcgis desktop - Calculate Sequential Values based on Boolean field - GIS SE.

Looking to the section of the SQL standard that defines the behavior of cursors, ISO/IEC CD 9075-2 Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation), it clearly states (at least through SQL:99 that I have seen in person, but I assume the same language exists in later editions):

When the ordering of a cursor is not defined by an <order by clause>, the relative position of two rows is implementation-dependent.

The importance of that statement can't be emphasized enough. Without including an explicit SQL ORDER BY clause, the user is leaving the order of records returned in a cursor up to the application or database management system. In this specific situation with ArcGIS Pro, Esri does not document how Calculate Field constructs the underlying SQL used to create the cursor. Additionally, many DBMSs explicitly state that no consistent ordering is guaranteed without including a SQL ORDER BY clause. For example, from Microsoft SQL Server SELECT - ORDER BY Clause (Transact-SQL):

The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified.

With vendors either not documenting their ordering rules or saying no order is guaranteed without a SQL ORDER BY clause, a user can't know for certain the order ahead of time, and worse, the order may change over time even for the same data set.

If one wants or expects a certain order of records, the only way to guarantee it is by using a SQL ORDER BY clause.  For file geodatabases, the unspecified order commonly is by ObjectID, but Esri has never stated that is a guarantee so they feel no need to share the internal mechanics of what may change row ordering in a FGDB table.