Identity generator: SequenceGenerator for SQL Server based on sp_sequence_get_range

[janv8000]

Hello,

Has using the sp_sequence_get_range [1 ]sproc been considered as a possible source for sequence based identities?

Right now we are using seqhilo in our mapping files. This works perfectly fine within NHibernate, but if we want to write some custom SQL we can't naively use SELECT NEXT VALUE FOR <sequenceName> because this returns for example 5, but that doesn't mean that 5 isn't already taken as a unique id because of the hilo magic.

If NHibernate could directly request ranges from the database instead of inventing ranges with the hilo algorithm, SELECT NEXT VALUE would always return some id that has (99.999%) not been used before ...

[1]: sp_sequence_get_range (Transact-SQL) - SQL Server | Microsoft Learn

[fredericDelaporte]

I do not think this has been considered. sp_sequence_get_range is obviously a vendor specific artifact, while NHibernate supports many different DB and do so by using standard features as much as possible.

hilo is not a sequence id generator. It may use a sequence for its underlying implementation, but that sequence only represents the high value and cannot be used without a logic to handle the low value. So for allowing inserting values with some custom relatively simple SQL, that is not suitable, whatever the flavor of hilo algorithm used.

It sounds to me the enhanced sequence generator with an increment_size is near your need. At least it would allow you to get an usable unused id, but it will waste increment_size - 1 ids by doing so. It could still be suitable if your direct SQL cases are infrequently used.

Otherwise you would need to provide your own "sequence by range" generator, likely by copying NHibernate.Id.Enhanced.SequenceStyleGenerator and changing it.

Additional options could be added in it in NHibernate for supporting this too, fell free to submit a PR. It should be taken into consideration that this generator is meant to have fallback implemented if the dialect does not support the required feature. So, it may be not trivial to implement.