When using a HiLo ID generation strategy, what types should be used to hold Ids?

Question

I'm asking this from a c#/NHibnernate perspective, but it's generally applicable. The concern is that the HiLo strategy goes though id's pretty quickly, and for example a low record-count table (Such as Users) is sharing from the same set of id's as a high record-count table (Such as comments). So you can potentially get to high numbers quicker that with other strategies. So what do people recommend?

Code side: int/uint/long/ulong?

DBSide: int/bigint?

My feeling is to go with longs and bigingts, but would like a sanity check :)

Answer 1

You're probably safe with long.

Anyway, you don't have to use the same table for all entities. You can set the table parameter on each generator to a different value.

Answer 2

Complementing Diego's answer..

You can set table parameter on each generator, but IMO it results in an unecessary number of tables just to hold IDs.

I'd probably recommend using one row per entity (in the same table). With optional parameters you can configure HiLo algorithm and it will lock only the necessary line. You have distinct seeds to each entity. In FluentNH, it would be something similar to:

        Id(x => x.Id, "Id")
            .GeneratedBy.HiLo("IdsTable", "id", "20", 
                p => p.AddParam("where", "table = 'mappedent'"));

Despite that, you can also avoid the where extra param and just set a different column (what allow you to have differente ID types even in DB). It will bother you, though, if you have a big number of entities (because of DBs column nr limitation). Remeber, too, that every single entity requesting an ID range will lock all others, what can lead you to deadlocks in some situations.

Hope this helps!

Regards,

Filipe