topic Re: Re-Order Fields in an SDE Geodatabase in Data Management Questions

Re-Order Fields in an SDE Geodatabase

TimHayes — Fri, 25 Jun 2010 15:38:27 GMT

Is it possible to do this in 9.3.1? I have done this in MS Access with a Personal Geodatabase.

Re: Re-Order Fields in an SDE Geodatabase

VinceAngelo — Fri, 25 Jun 2010 16:36:55 GMT

Short answer: No.

Essay form: ArcSDE isn't a database, it's an interface to databases. The only
way to change table column order with the ArcSDE API is to export in the desired
order and create a new table with the result. If, as in your example, you can
coax the database to reorder the columns, then ArcSDE will present them in that
order, but a brief web search indicates that SQL-Server, PostgreSQL and Oracle
do not permit this as a trivial operation. Many of the referenced links recommend
using a view, which will work, but ArcGIS puts limits on geodatabase participation
by views. If you're going to have to reload the table anyway, consider doing some
row-order optimization as well, by exporting in spatial index order.

- V

Re: Re-Order Fields in an SDE Geodatabase

TimHayes — Fri, 25 Jun 2010 17:22:02 GMT

Yikes! I never knew something so simple could be so complicated. I wonder why it is so easy to do with in MS Access and Excel. Microsoft must have a secret they are not sharing. Thanks for the reply. We will stick with what we got.

Short answer: No.

Essay form: ArcSDE isn't a database, it's an interface to databases. The only
way to change table column order with the ArcSDE API is to export in the desired
order and create a new table with the result. If, as in your example, you can
coax the database to reorder the columns, then ArcSDE will present them in that
order, but a brief web search indicates that SQL-Server, PostgreSQL and Oracle
do not permit this as a trivial operation. Many of the referenced links recommend
using a view, which will work, but ArcGIS puts limits on geodatabase participation
by views. If you're going to have to reload the table anyway, consider doing some
row-order optimization as well, by exporting in spatial index order.

- V

Re: Re-Order Fields in an SDE Geodatabase

VinceAngelo — Fri, 25 Jun 2010 19:05:58 GMT

Neither Access nor Excel are relational database management systems (RDBMS); since they
can keep all data in memory and just rewrite, there isn't any real magic involved.

If your source table is spatially fragmented (that is, there is no correspondence between
location and row number -- when you draw a single layer at 1/4-1/10 of the total extent
and features render in apparently random order), then there could still be a large benefit
to draw (and other spatial search) performance with row optimization. It only becomes
obvious on tables with a 300k+ rows or more, but the difference is measureable with as
little as 10k rows.

- V