2

I'm working with an enterprise MS SQL Server geodatabase which is accessed via SDE. ArcGIS manages ObjectId values such that they are uniquely assigned as features are added to the geodatabase.

My question is around what happens if a feature (say with ObjectId: 1234) is deleted. Is it a certainty that there will never be a new feature generated with ObjectID: 1234? ie, are previously-assigned ObjectId values ever re-used?

1
  • 3
    Relying on the objectid field will bite you. For example, exporting/importing the dataset (as in backup/restore) may very well reset the keys for the involved layers. The "have fun" factor increases when the DBA decides to change how data is moved around without consulting first, or when someone realize the joined tables contains unrelated information. Over the long term, is if faster and simpler to add a new field that you fully control to hold your PK. – JGH Jul 13 '20 at 12:48
4

The implementation differs by Enterprise geodatabase platform (RDBMS), but all OBJECTID (aka "registered rowid") type columns defined by ArcGIS will always be 32-bit integer values, fed by a SEQUENCE or other database equivalent that allocates ID values in batches (with varying batch size, by platform) from 1 to 32-bit signed INT_MAX (231-1). The values are generally sequentially assigned from batches (e.g. 1-400, 401-800), but when an edit session ends without allocating all the values from the batch, the intervening values can be either cached or skipped (again, depending on the implementation). Since concurrent editing is supported, it is not safe to assume that because the previous allocated value was 400, the next one will be 401.

Geodatabase implementations make no effort to track deleted IDs, and Esri-supported allocation methods do not reuse IDs in any way (attempting to override allocation algorithms for reuse would void the warranty on your geodatabase*).

Once the allocation sequence reaches 2147483647, overflow (and INSERT failure) would occur on the next INSERT. Negative and zero rowid values are not permitted (assigning negative or zero rowid values would also void the warranty*).

The documentation has evolved over time so only snippets of the original documentation that made this clear exist, but the What is an ObjectID? and Fundamentals of ObjectID fields pages assert uniqueness and signed 32-bit range.

In certain high-volume data applications, partitioning could be utilized to simplify bulk DELETE operations. It would be unsupported (breaking that warranty* again), but not unheard of for the Esri ID allocation model to be tweaked by SQL to populate values significantly lower than the the current maximum rowid value (e.g. loading 49m rows each night, but only needing the last three nights of data, IDs could be allocated to 1-49m, then 50m-99m, 100m-149m, and back to 1-49m). But this sort of application is rare and requires close attention to prevent geodatabase corruption.

It's probably worthwhile to note that using the TruncateTable tool (ArcPy TruncateTable_management) will generally reset the start point in the rowid allocation scheme, so rowids can be reused, but only in situations where conflict cannot occur.

The above is for registered Enterprise geodatabase feature classes. But ArcGIS clients now also support Query Layers, for which unique ID assignment is more flexible (most non-float columns can be used, and compound keys are permitted) and user-managed (incuding a user-assigned ID allocation scheme). Using a SEQUENCE or IDENTITY or SERIAL (as supported by the RDBMS) to populate these values is common, but not required. So it's conceivable that an ID reuse scheme could be implemented. It would almost certainly be slower, but in that case, increasing the available range of IDs would be the focus, and the performance therefore not significant.


*There is no warranty on geodatabases.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.