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.

So the high-value must be set to:

high-value = ceiling(current-identity / max-lo)

---

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.

Answer 2

If this is a question about migrating an existing application to hilos which previously used auto ids, and has old data in it which needs to be migrated... then this would be my best bet (not tried it though! - comments welcome!) :

• Change your column types ids to bigints
• find out highest id value currently in any table.
• Set your 'next-high' value in the hilo source table to a value higher than you found in the IDs

If course this only addresses issues with the identity column, not anything else in your schema that might need to change if you are moving an app to NHibernate.