Reiterate Database Sequence

Question

Hello all. Does anyone know if it is possible to reiterate a database sequence upon reaching a specified cap/maximum? We have it coded in SQL to create unique FacilityIDs for our assets with the format: "_DDMMYYYY_<6-digit number>". I would like for the last 6 numbers to reset to the initial starting point of the Database Sequence in use upon the sequence breaching the number 999999. I found some walk-throughs on how to put this together in Arcade using the CreateDatabaseSequence gp with the exception of being able to reset the value of the Database Sequence.

JohannesLindner · Answer

You can't use Arcade to reset the sequence, but you can do it without a sequence:

// Calculation Attribute Rule
// field: FacilityID
// triggers: insert

// get the feature that was created last
var fs = Filter($featureset, "FacilityID IS NOT NULL")
var last_f = First(OrderBy(fs, "OBJECTID DESC"))
if(last_f == null) { return $feature.FaciltyID }

// get its sequence value
var old_seq = Number(Split(last_f.FacilityID, "_")[-1])

// get the new sequence value
var new_seq = IIf(old_seq + 1 > 999999, 1, old_seq + 1)

// construct and return the new FacilityID
var s = Text(new_seq, "000000")
var d = Text(Today(), "DDMMY")
return `ABC_${d}_${s}`

rzufelt · Answer

I don't use database sequences because I got tired of the "gaps" in the sequence whenever a feature got created, but not saved, etc.

SQL server can easily script the drop/create of the database sequence.  No example (and haven't tested), but one option would be to put an insert trigger on the table and check if the number has reached 999999, if so, drop/re-create the sequence.

You can follow the image below to see how to have SSMS create the drop/create SQL for you:

R_