2

Has anyone implemented a way of creating an auto-incremented integer field within an ArcSDE (SQL Server 2008 R2) feature class / table?

I don't want to rely on the OBJECTID field because if a feature class is exported to another geodatabase, the OBJECTIDs get re-calculated. I'm looking for a permanently stored integer field that increments as records are added to the feature class / table.

  • 1
    Whenever I need to do something data related, I never rely on esri products. I would recommend going directly to SQL Server and creating the auto-incrementing field there for that table... – janechii Sep 18 '14 at 20:10
  • I guess that's what I'm asking...how would you go about creating an auto-incrementing field within SQL Server and then populate that auto-incrementing value within your feature class? – boyle.matt Sep 18 '14 at 20:30
1

This is how I've done it in the past. Not sure how long the command line sdelayer will be around though.

In SQL Server:

CREATE TABLE myFC (
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    OtherData VARCHAR(10),
    SHAPE Geometry -- Could be a Geography
);
CREATE SPATIAL INDEX myFC_sdx ON myFC (SHAPE) -- Make your own spatial index
USING GEOMETRY_GRID
WITH (
    BOUNDING_BOX =(0, 0, 100000, 100000) --Use some sensible bounds
    ,GRIDS=(HIGH, HIGH, MEDIUM, MEDIUM)
    ,CELLS_PER_OBJECT=100
);

From a command line:

sdelayer -o register -l myFC,SHAPE -C ID,USER ... connection information, etc

SDELayer reference

Note: When you export this to another geodatabase these IDs will not increment there. I am assuming that you are talking more about publishing rather than distributed editing though.

This will not work with Feature Classes registered as versioned.

  • Interesting...so, if you create the table within SQL Server first and assign an Identity column and then register the table using the command line the Identity seems to persist and work...? – boyle.matt Sep 18 '14 at 21:17
  • 1
    @boyle.matt I've had no issues with it, but this will not work if you have versioning , due to the A and D tables that SDE uses. resources.arcgis.com/en/help/main/10.1/index.html#//… – MickyT Sep 18 '14 at 21:25
  • Does this only work with data NOT registered as versioned? – boyle.matt Sep 18 '14 at 21:43
  • Yes. If you require versioning then another method will need to be investigated. You could use the Add Global ID option, but I am not a fan of them. Another option could be to persist the objectid to another column. – MickyT Sep 18 '14 at 21:59
  • I tried your workflow today. I created a table within SSMS, gave it an ID field (int) and set the identity to 1,1 and also set it as the primary key. I added a couple dummy fields and a geometry field. I then registered the table with the geodatabase through ArcCatalog. Made a few edits and the ID field sequentially numbered fields. I then versioned the feature class and tried making some edits and the identity property within the ID field seeemed to keep working. The A delta table showed the sequential numbers. I rec-posted and compressed the feature class, it seemed to work. – boyle.matt Sep 19 '14 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.