Why are short integer fields on SDE auto-updating to Precision 5?

Question

I am having a perceived small issue in Server for ArcGIS (10.3.1 and 10.4.1 on SQL Server) where whenever a Short Integer field is added to a feature class, the Precision auto-updates to Precision: 5 regardless of configuration when created.

In the screenshots below, I have added a short integer field; InstallYear. InstallYear has a Precision of 4.

Once I save the changes and navigate back to the properties of the feature class, you can see that it has auto-updated upon save to Precision: 5. The field was added in ArcCatalog. I have also tested this by adding a field in ArcMap with the same result.

File geodatabases short integer precision cannot be altered. However, I tested it on a shapefile and the configured precision does save.

Can anyone explain why short integer precision is auto-updated to 5 on SDE, regardless of configuration? Referencing an Esri document, short integer precision of 1-5 is supposedly supported on SQL Server unless I am reading that incorrectly.

Answer 1

The situation is strange because it's complex. Remember that SDE predates ArcGIS, as do dBase and Oracle. Therein lies the problem.

dBase has two potential encoding schemes for numeric data. Oracle has one encoding scheme for all numeric types (both floating-point and integer).

For a long time, SDE (which uses an ODBC model, and classifies numeric data by single/double/int/short) had to work around the inherent conflict between a NUMBER(5) supporting values larger than would be supported by a 16-bit integer (and NUMBER(10) permitting values larger than a 32-bit integer). The details have drifted into the mists of time, but I seem to recall two rounds of "short is width 4" and "short is width 5", possibly sandwiched around introduction of a 64-bit integer type (which ArcGIS Desktop doesn’t support).

Then there were similar fluctuations with dBase. The irony is that most of the other database implementations support fixed types (where this wouldn't matter), but because ArcGIS has to support both Oracle and dBase, it gets wrapped around the axle with how to map datatypes that permit the best possible range or the safest conversion.

I'd recommend that you find a mapping that works, be it by explicitly using CREATE TABLE to generate tables, or by ignoring width changes that don't actively hurt you. Life is too short to work out the whyfores of two equally compelling schools of thought.