topic Relevance of column order in a SQL Server table in Data Management Questions

Relevance of column order in a SQL Server table

JohnCuthbertson — Wed, 05 Mar 2014 02:03:22 GMT

A typical table created in SQL Server Geodatabase 10.0 (with default type of SDE_BINARY) has the following columns..

[SHAPE] [int] NULL

After you issue 'sdetable -o register' and 'sdelayer -o add' you get an additional column called 'FID', so the columns look like
[SHAPE] [int] NULL,
[FID] [int] NULL

If you then issue MIGRATE command with -'k geometry' the columns look like..

[FID] [int] NOT NULL,
[SHAPE] [geometry] NULL

Note that the SHAPE field is now GEOMETRY (as expected) and that the FID and SHAPE column order has been reversed.

This reversal of column order appears to be transparent to the majority of ESRI functions.

However we have a program that issues the 'SE_stream_set_shape' function call from 'ARcSDE 10.0 SDK' that gets a 'a -114 error (Wrong column type)' when accessing the migrated table. Manually altering the order of the FID and SHAPE columns fixes the problem. I am assured by the developer that he is dynamically locating the SHAPE column and is not relying on any preconceived column order.

My question then is 'does the ESRI code (in particular, the 'SE_stream_set_shape' function, rely in any way on the fact that the FID column must be the last column in the table'.

Re: Relevance of column order in a SQL Server table

VinceAngelo — Wed, 05 Mar 2014 03:25:41 GMT

No, this is a programmer error issue. SE_WRONG_COLUMN_TYPE can
only be generated by using a setter/getter with an assumed type order.
After creation, the SE_STREAM should be polled for column type by
position with SE_stream_describe_column() over the range 1 to the
count returned by SE_stream_num_result_columns().

Using the setter/getter functions is also measurably slower than using
SE_stream_bind_{input|output}_column (but failure to identify types
correctly with a bind will cause a segmentation violation).

I should note that the 'C' API is now deprecated, and won't be available
post-10.2.2.

- V

Re: Relevance of column order in a SQL Server table

JohnCuthbertson — Thu, 06 Mar 2014 00:11:35 GMT

Vince, thanks for your input, especially regarding deprecation.
We managed to get around our problem by using our own unique key column rather than having sdetable generate the FID column

sdetable -o register -c OBJECTID -C USER

Once we did this the problem magically dissappeared