Re-Order Fields in an SDE Geodatabase

Question

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

VinceAngelo · Answer

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

THayes2000 · Answer

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

VinceAngelo · Answer

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