Auto incrementing ID field alternative to ObjectID

JillSlater

I am looking for suggestions for how to implement an auto-incrementing ID field as an alternative to ObjectID in an Arcgis geodatabase 10.2.1 and SQL server 2008. We have learned the lesson to not use ObjectID as a feature identifier on the user end of projects because the ObjectID will not be retained if we need to migrate data.

I know it is not possible to use the Identify function in SQL on a versioned dataset. We are exploring using a trigger on the adds table to auto generate based on the previous record, but I am not sure the logic will work. Has anyone had any success creating a separate auto generating ID field?

For our project this needs to happen on the backend in the SQL server because we have both a mobile app with offline editing, and also a web app all editing the same data source.

ThomasColson

The following assumes you have a unique ID field (SQL, GUID in SDE), that in SQL is set to "newsequentialid()". Lets assume the field is called "LocationID". Note how that column is NOT INCLUDED in this trigger! In the Adds table, it is a NON-SEQUENTIAL ID, but when it hits the business table, it becomes sequential. The rest of the trigger deals with using SQL geometry operations to autopopulate some columns. This will ONLY work on data that is versioned with move edits to base checked, which kills any replication plans you have. If you can't move edits to base, you'll need to wrap this up in a stored proc using a cursor, which IMHO was more work than it's worth.

 
CREATE TRIGGER [dbo].[INSERT_VM_PLOTS_Location_ID]
ON [dbo].[VM_PLOTS]
INSTEAD OF INSERT NOT FOR REPLICATION
AS BEGIN
SET NOCOUNT ON; 
INSERT dbo.VM_PLOTS(
OBJECTID, VError, MapSource, SourceDate, MapMethod, QuadName, UnitCode, EditDate, UnitName, IsExtant, HError, County, State, LAT, LON, Watershed, PlaceName, TrailName, StreamName, Landform, Meta_MID, Road, Elevation, Year, Notes, Coord_Units, Coord_System, Datum, UTM_Zone, ManagementZone, ParkDistrict, EditBy, CreateBy, CreateDate, RegionCode, Restriction, Soil, Geology, HistoricDisturb, Veg, Level4EcoReg, AvgRain, MeanDailyTemp, Loc_Name, Directions, Description, Loc_Notes,X_Coord, Y_Coord, Shape
)
SELECT 
a.OBJECTID, a.VError, a.MapSource, a.SourceDate, a.MapMethod, b.NAME, a.UnitCode, a.EditDate, a.UnitName, a.IsExtant, a.HError, c.NAME, d.NAME, a.LAT, a.LON, e.HU_12_Name, a.PlaceName, a.TrailName, a.StreamName, a.Landform, a.Meta_MID, a.Road, a.Elevation, a.Year, a.Notes, a.Coord_Units, a.Coord_System, a.Datum, a.UTM_Zone, g.FCSubtype, f.District, a.EditBy, a.CreateBy, a.CreateDate, a.RegionCode, a.Restriction, j.MUNAME, k.GLG_SYM, l.CLASSES, h.VitalName, p.EPA_REGION, q.RANGE, a.MeanDailyTemp, a.Loc_Name, a.Directions, a.Description, a.Loc_Notes,a.X_Coord, a.Y_Coord, a.Shape
From
(SELECT 
OBJECTID, VError, MapSource, SourceDate, MapMethod, QuadName, UnitCode, EditDate, UnitName, IsExtant, HError, County, State, LAT, LON, Watershed, PlaceName, TrailName, StreamName, Landform, Meta_MID, Road, Elevation, Year, Notes, Coord_Units, Coord_System, Datum, UTM_Zone, ManagementZone, ParkDistrict, EditBy, CreateBy, CreateDate, RegionCode, Restriction, Soil, Geology, HistoricDisturb, Veg, Level4EcoReg, AvgRain, MeanDailyTemp, Loc_Name, Directions, Description, Loc_Notes,
 SHAPE = CASE WHEN SHAPE IS NOT NULL   
        THEN SHAPE ELSE Geometry::STPointFromText('POINT('  
          + CAST(X_Coord AS VARCHAR(20)) + ' '   
          + CAST(Y_Coord AS VARCHAR(20)) + ')', 26917) END,  
      X_Coord = CASE WHEN SHAPE IS NULL THEN X_Coord ELSE SHAPE.STX END,  
      Y_Coord = CASE WHEN SHAPE IS NULL THEN Y_Coord ELSE SHAPE.STY END
FROM inserted) 
AS a 
LEFT OUTER JOIN grsm.dbo.USGS_24K_TOPOMAP_BOUNDARIES AS b 
ON b.Shape.STContains(a.Shape) = 1 
LEFT OUTER JOIN grsm.dbo.GRSM_COUNTIES AS c 
ON c.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN grsm.dbo.GRSM_STATES AS d 
ON d.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN grsm.dbo.WBD_12 AS e 
ON e.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN grsm.dbo.RANGER_DISTRICTS AS f 
ON f.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN IANDM.dbo.GRSM_VEG AS h 
ON h.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN grsm.dbo.GRSM_GMP_ZONE AS g 
ON g.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN iandm.DBO.GRSM_Soil_Taxonomy AS j
ON j.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN IANDM.dbo.grsmglg AS k
ON k.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN IANDM.dbo.GRSM_DISTURBANCE_HISTORY AS l
ON l.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN IANDM.dbo.GRSM_LEVEL_IV_ECOREGIONS AS p
ON p.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN IANDM.dbo.AVERAGERAINFALL as q
ON q.Shape.STContains(a.Shape) = 1;
end
 

I take it one step further and get this all going in SQL/SDE, without declaring a globalID in Arc Cat, then change the object flags on the "LocationID" column in sde_column_registry to "256", thus giving myself a sequential globalid. The difference in performance is immediate and drastically improved.