topic Re: Generation of row id (object id) with ArcSDE and MS SQL Server in Data Management Questions

Generation of row id (object id) with ArcSDE and MS SQL Server

LesSears1 — Wed, 12 Oct 2011 18:57:12 GMT

Can anyone tell me how SDE managed row ids are generated when using MS SQL Server?

When using Oracle, SDE creates a sequence which is used for creating the next row id, but SQL Server does not have sequences, so another mechanism must be employed.

Thanks.

Re: Generation of row id (object id) with ArcSDE and MS SQL Server

VinceAngelo — Wed, 12 Oct 2011 22:13:56 GMT

A one-row "i table" is created, with a trigger to increment it like a sequence.

- V

Re: Generation of row id (object id) with ArcSDE and MS SQL Server

LesSears1 — Thu, 13 Oct 2011 11:06:54 GMT

Thanks.

Can you tell me how to determine which "i" table goes with which feature class table?

Re: Generation of row id (object id) with ArcSDE and MS SQL Server

VinceAngelo — Thu, 13 Oct 2011 11:18:25 GMT

It's exactly the same as in Oracle (and the other databases):
+ Rowid 'N' corresponds to the table_registry ID key
+ Layer 'N' corresponds to the layers ID key
+ Raster 'N' corresponds to the raster_columns ID key

- V

Re: Generation of row id (object id) with ArcSDE and MS SQL Server

LesSears1 — Thu, 13 Oct 2011 13:48:34 GMT

Vince,

I'm sorry, but I'm not sure I know what you mean in the last post.

The "i" tables have id_type, base_id, num_ids and last_id and from the previous post I understood that there would be an "i" table for each feature class with with a single row that is used to keep track of the next available id.

If I understood that correctly, how do I know which "i" table is used for which feature class? Is the number after the "i" in the table name simply the table registry id in the same way that the Oracle sequences are named "R" plus the table registry id?

Thanks.

Re: Generation of row id (object id) with ArcSDE and MS SQL Server

VinceAngelo — Thu, 13 Oct 2011 14:50:46 GMT

Yes, ArcSDE uses a rigid naming paradigm -- anyplace there is <sometext><somenumber> naming,
you should look to the corresponding <something>_id table's row for the parent object. In the case
of iN, look for the N in table_registry. The documentation covers this as well, and includes the caveat:

Note:
You will notice that once you have loaded data or run sdesetup, you have several i tables and stored procedures in your database. These stored procedures and i tables are used for generating feature IDs for feature classes. The i tables contain a number in their name. This number corresponds to the registration_id of the feature class in the SDE_table_registry table. Editing these tables or stored procedures is not supported and is highly discouraged.

- V