SQL Server equivalent possible? about idgen

[RobThree]

There may be other ways to achieve this. AFAIK you can load .Net assemblies into SQL server. I'm not sure about the lifetime of these loaded objects (do they only exist within the scope of the function or are they loaded once, globally and kept around) or other challenges you may run into but it may be worth looking into.

On resetting the Sequence you were referring to: theoretically you wouldn't have to. It might not be the exact same behaviour as IdGen implements, but you could just let it count up and either have it overflow and 'wrap around' or reset it at another given interval entirely (with careful consideration ofcourse).

[RobThree]

I think the biggest challenge would be to keep some sort of state between invocations for the sequence-part of the ID. You could -ofcourse- store that in a table or something but I'm not sure if that would be efficient and although I have some experience with SQL Server, I couldn't tell you of the top of my head if it would be possible to store a global 'counter variable' in memory (without concurrency issues). Other than that I don't see why it wouldn't work as a SQL server function; you could even leave all the 'variable width' of the fields and just use some fixed values which should make things a little easier.

So, the theory works, a server should be able to generate an ID without collisions as long as it has a reserved GeneratorId; I'm just not so sure about the Sequence part.

[vyrotek]

Indeed, that's the part I was wondering about too. One might be able to do it using/resetting SQL Server's Sequence feature but it would be quite a hack. Snowflake IDs are very powerful, but we've been reviewing the trade-offs vs. traditional Identity or GUIDs and one concern is giving up SQL generated IDs. Can't have it all, I suppose. 😅

Thanks for your thoughts!