I am converting a class that uses the Identity generator to one that uses hilo. I'm also using a single table with distinct row per entity:
EntityId (table)
- EntityName
- NextHigh
Old Table:
Patients (table)
- Id (identity)
New Table:
PatientRecord (table)
- Id
To retain data integrity, I just use the existing Patients.Id as the new PatientRecord.Id:
insert into PatientRecord (Id)
select Id from Patients
And create an EntityId entry:
insert into EntityId values ('PatientRecord', ??)
where ?? is the next hi value. What value should I use here? By default, initializing the column would be 1. Do I just use that, or should I use something like select MAX(Id) from PatientRecord?
The next_hi is like a session key used for multiplication for generating the identity (id) so you can insert whatever you want like for example 1 or 2 or 10 each user increments the next_hi and with it it generates all identitys until some max value and then again asks for another next_hi by incrementing it... with this strategy all identity's are unique ... and they can be generated locally
Is there a practical way of migrating from identity columns to hilo keys?
http://fabiomaulo.blogspot.com/2009/02/nh210-generators-behavior-explained.html