idea Ammend ArcGIS documentation to clarify whether adding fields with T-SQL is supported in Data Management Ideas

Ammend ArcGIS documentation to clarify whether adding fields with T-SQL is supported

ThomasIsraelsen — Tue, 18 Oct 2022 18:00:34 GMT

Today it is possible to add a field to a registered table or feature class in a SQL Server Geodatabase outside of ArcGIS, for example through T-SQL or with Management Studio. Such fields appear to be recognized by ArcGIS and they appear magically in sde.SDE_column_registry.

But it is not at all clear to me if this is a supported workflow. As far as I can tell, the documentation is silent on whether I can do this or not.

I need it clarified explicitly because I have been told by support, that since this workflow is not explicitly described in the docs, they will not provide any assistance, for such databases, should I ever need it.

The reason why I want to create fields through another mechanism than ArcGIS is that ArcGIS has a really weird mapping of the ArcGIS floating point field types to SQL Server data types. One that is useless for me.

When a user creates an ArcGIS "Double" field type in SQL Server, we do not get a SQL Server "float" field, which I think most would expect. Instead we get the weird, inefficient and inaccurate accounting-style data type "numeric"/"decimal". Same for the ArcGIS field type "Float". This should map to a SQL Server "real" but also results in "numeric"/"decimal".

Re: Ammend ArcGIS documentation to clarify whether adding fields with T-SQL is supported

George_Thompson — Thu, 27 Apr 2017 19:30:25 GMT

Here is some documentation on editing the data via SQL (T-SQL for SQL Server). What type of data can be edited using SQL?—Help | ArcGIS Desktop

It is not recommended to edit versioned data (ever) outside of a supported workflow. This could cause corruption within the Geodatabase and may not be apparent till a later date. Editing of versioned data can be done via the versioned view: What is a versioned view?—Help | ArcGIS Desktop

I can tell you from experience that using unsupported workflows can make the GDB corrupt and cause you to have a bad day. What support is basically saying is this: If you use an unsupported workflow, it may be impossible for them to figure out what happened and/or provide a correction.

Hope this helps!

Geodatabase‌

Re: Ammend ArcGIS documentation to clarify whether adding fields with T-SQL is supported

ThomasIsraelsen — Thu, 27 Apr 2017 20:42:00 GMT

Thanks for commenting.

I am not actually looking to edit data from SQL - I am aware of the restrictions there. And I am not working with versioned data at all.

But thank you for the link. The next article in the TOC is SQL access to enterprise geodatabase data and that actually provides the answer that apparently neither Support nor I could find:

Note: Do not use SQL to alter the schema of datasets stored in your enterprise geodatabase.

It was not the answer I was hoping to find, but at least it is clear.

My own experience is that adding fields outside of ArcGIS works just fine, and indeed they are discovered by ArcGIS and included in the SDE metadata. But I understand that if it is not supported then it is not supported 🙂

I guess my next idea here will be to have Esri support this workflow, since it appears to work.

And after that for Esri to properly support the actual floating point data types in SQL Server rather than just the weird accounting ones.

Re: Ammend ArcGIS documentation to clarify whether adding fields with T-SQL is supported

George_Thompson — Fri, 28 Apr 2017 12:44:49 GMT

Yep, When it comes to versioned data ONLY use the supported workflows via the ArcGIS Client or versioned views.

Re: Ammend ArcGIS documentation to clarify whether adding fields with T-SQL is supported

ThomasIsraelsen — Sat, 29 Apr 2017 12:04:48 GMT

Like I said:

I am NOT using versioned data.

Re: Ammend ArcGIS documentation to clarify whether adding fields with T-SQL is supported - Status changed to: Closed

SSWoodward — Thu, 13 Oct 2022 19:59:22 GMT