topic Re: Object name lengths on SQL Server? in Data Management Questions

Object name lengths on SQL Server?

JasonBirch — Wed, 18 Sep 2013 20:46:56 GMT

Hi all! Get ready for a long first post...

We're in the progress of migrating to ArcSDE, starting with 10.1 running on SQL Server 2012 using an SDE-schema geodatabase. One of the staff here has raised a concern about the maximum length of database name, schema owner, and feature class name in this scenario. My initial implementation includes objects with compound names which are somewhat long (worst case):

NanaimoSDE.Engineering.AdministrativeAreaBoundaryLine

Searching indicates that at some point in the past this concern may have been valid, where SDE may have limited table names to 32 characters. However, current guidance from the Defining Feature Class Properties help topic seems to be:

"The length of feature class and table names depends on the underlying database. The maximum name length for file geodatabase feature classes is 160 characters. Be sure to consult your DBMS documentation for maximum name lengths."

Digging deeper, the SDE_table_registry table defines the component parts of the feature class as:

[INDENT]database_name: nvarchar(32)
owner: nvarchar(32)
table_name: sysname - where sysname is a system type equal to nvarchar(128)
[/INDENT]

With this in mind, it appears that we should be safe with our current structure, with the primary constraints being to keep any future schema owner names under 32 characters, and table names under 128 characters.

I would really appreciate any guidance on whether this confidence is unfounded, and if there may be Esri or third-party components which still rely on shorter object names, and whether the total length of Database+Owner+Table is subject to any additional constraints.

I personally prefer to use descriptive object naming, but we're early enough in the implementation process that I can re-create the database and schema owners with shorter identifiers if required.

Thanks!

-J

Re: Object name lengths on SQL Server?

VinceAngelo — Sat, 11 Dec 2021 23:19:33 GMT

There's "descriptive" and then there's "tediously long". Unfortunately, that naming scheme drifts into the latter category.

ArcSDE was ported to SQL-Server from Oracle, and needs to maintain a least common denominator on naming lengths across all RDBMS products.
The current constants used throughout ArcSDE code are:

#define SE_MAX_COLUMN_LEN          32  /* MAXIMUM COLUMN NAME LENGTH */ 
#define SE_MAX_TABLE_LEN           160 /* MAXIMUM TABLE NAME LENGTH */ 
#define SE_MAX_SCHEMA_TABLE_LEN    30  /* MAXIMUN TABLE 'ONLY' NAME LENGTH */ 
#define SE_MAX_ALIAS_LEN           32  /* MAXIMUM TABLE ALIAS LENGTH */ 
#define SE_MAX_ENTITY_LEN          256 /* MAXIMUM ENTITY TYPE LENGTH */ 
#define SE_MAX_HINT_LEN            1024/* MAXIMUM DBMS HINT LENGTH */ 
#define SE_MAX_SCHEMA_LEN          32  /* MAXIMUN SCHEMA NAME LENGTH */ 
#define SE_MAX_OWNER_LEN           32  /* MAXIMUM TABLE OWNER NAME LENGTH */ 
#define SE_MAX_INDEX_LEN           160 /* MAXIMUM INDEX NAME LENGTH */ 
#define SE_MAX_GROUP_LEN           128 /* MAXIMUM GROUP NAME LENGTH */ 
#define SE_MAX_COLUMNS             500 /* MAXIMUM NUMBER OF COLUMNS */ 
#define SE_MAX_ANNO_TEXT_LEN       255 /* MAXIMUM ANNOTATION TEXT LENGTH */ 
#define SE_MAX_VERSION_LEN         64  /* MAXIMUM VERSION NAME LENGTH */ 
#define SE_MAX_OSNAME_LEN          64  /* MAXIMUM OPERATING SYSTEM NAME LENGTH */ 
#define SE_MAX_OSVERSION_LEN       128 /* MAXIMUM OPERATING SYSTEM VERSION LENGTH */ 
#define SE_MAX_DBMSVERSION_LEN     128 /* MAXIMUM DATABASE VERSION LENGTH */ 
#define SE_MAX_VERSION_INPUT_LEN   62  /* MAXIMUM USER-SUPPLIED VERSION NAME LENGTH */ 
#define SE_MAX_OBJECT_NAME_LEN     160 /* MAXIMUM OBJECT NAME LENGTH */

In practice the limits are generally one space *less* than the _LEN value (to allow for a NUL
terminator) -- although this does not apply to SE_MAX_SCHEMA_TABLE_LEN.

- V

[Edited to format include file contents legibly]

Re: Object name lengths on SQL Server?

JasonBirch — Thu, 19 Sep 2013 05:37:20 GMT

Thanks Vince, I really appreciate you taking the time to provide a definitive answer!

From this, it looks like the table name in my example ( AdministrativeAreaBoundaryLine ) is pushing the limit on the SE_MAX_SCHEMA_TABLE_LEN constant. This table is from the Esri Canada municipal data model, and we're trying to implement it with only critical changes. Fortunately, I believe that it's the longest feature class, though there may be a couple related attachment tables that are longer.

Any advice on the database and schema names? Am I correct in assuming that the SE_MAX_TABLE_LEN is the maximum length of DB+Schema+Table? It appears that they (NanaimoSDE, Engineering) fit within the constraints of those constants. I could re-build the DB using shorter identifiers like CoNSDE.Eng.* if necessary, but not sure the effort is warranted.

I often wonder if my predilection for longer names stems from early trauma sustained under 8.3 limits and arcane file naming conventions.

-J

Re: Object name lengths on SQL Server?

MarcoBoeringa — Thu, 19 Sep 2013 09:50:29 GMT

If I were you, I would refrain from incorporating "geometry type" in Feature Class names, unless you maintain multiple geometry types (polygon, polyline) for what are in essence a single Feature Class and need unique names... (which probably isn't best practice, if you need to have a "boundary line", you can set a legend on a polygon Feature Class to only display the outline, instead of maintaining a seperate Feature Class with polylines).

ArcCatalog displays geometry type (polygon, polyline, point), through it's usage of unique icons, so there is no need to include something like "Line" in a Feature Class name. This saves you at least 4 characters, and if you skip "Boundary" as well (which will be obvious in the context of a polyline feature class for "areas"), you save another 8.

NanaimoSDE.Engineering.AdministrativeAreaBoundaryLine

Alternatives:
- NanaimoSDE.Engineering.AdministrativeAreaBoundary
- NanaimoSDE.Engineering.AdministrativeArea

Re: Object name lengths on SQL Server?

VinceAngelo — Thu, 19 Sep 2013 10:25:35 GMT

I usually incorporate geometry type into my table names, but I only devote one
character to the task ("P/L/A" suffix). I'll also use "Admin" for "Administrative"
and "Bound" or "Bnd" for "Boundary". Then I have room to include capture
scale ("5m", "100k") in the names, without exceeding the 30-character limit
for Oracle compatibility. I never bother with "SDE" in the database name.

- V

Re: Object name lengths on SQL Server?

JasonBirch — Thu, 19 Sep 2013 19:18:45 GMT

Great, thanks for the additional advice guys!

-J

Re: Object name lengths on SQL Server?

deleted-user-ZMHeMpwIlo8S — Tue, 14 Nov 2017 18:43:44 GMT

Would it be possible for a developer to modify the SE_MAX_COLUMN_LEN to a greater amount?

Re: Object name lengths on SQL Server?

VinceAngelo — Tue, 14 Nov 2017 19:00:01 GMT

No, that's an m4 macro constant in an include file (.h) within the source tree. Even if Esri still shipped the 'C' API, changing it in your install would have no effect on the Esri binaries (except where doing so corrupted structures, in which case it would have an effect -- bizarre segmentation violations and pointer alignment exceptions in your code).

- V