Is there a practical way of migrating from identity columns to hilo keys?

Question

I work with a database that depends heavily on identity columns. However as we have now moved all applications over to NHibernate I wanted to look into using HiLo as seems to be recommended with NHibernate. Are there any strategies to do this, or any common problems to watch out for?

Answer 1

You need to setup the table used by NH to create HiLo values correctly. Let Schema Creator create the table according to your mapping definitions, set the values according to the current state of the ids in your database.

I believe (you need to verify this) that values generated by hilo are calculated by:

hilo-id = high-value * max_lo + low-value

While the high-value is stored in the database, max_low defined in the mapping file and low-value calculated at runtime.

---

NHibernate also needs its own connection and transaction to determine and increment the high value. Therefore it does not work if the connection is provided by the application.

You can still use seqhilo, NH uses a database sequence to create next high-values and does not need a separate connection to do so. This is only available on databases which support sequences, like Oracle.

---

Correction:

Meanwhile, I had to implement it myself (before, it was just theory :-). So I come back to share the details.

The formula is:

next_hi = (highest_id / (maxLow + 1)) + 1

next_hi is the field in the database you need to update. highest_id is the highest ID found in your database. maxLow is the value you specified in the mapping file. No Idea why it is incremented by one. The division is an integer devision which truncates the decimal places.