Sorry in advance as this question is similar (but not the same!) to others.
Anyway, I need to be able to generate surrogate keys in more than one location to be synchronized at a later time. I was considering using GUIDs, however these keys may have to appear in the parameters of a URL and GUIDs would be really complicated and ugly.
I was considering a scheme that would allow me to use integers, providing better performance in the database, but obviously I cannot simply use auto numbers. The idea is to use a key with two meanings - the High-Low Strategy as I believe it is called. The key would consist of the source (where it was generated, generally 1 of 2 locations in this business case) and the auto incremented value. For instance:
1-000000567, 1-000000568, 1-000000569, 1-000000570, ...
And for another source:
2-000000567, 2-000000567, ...
This would also mean that I could store them in the database as integers (i.e "2-000000567" would become the integer "2000000567").
Can anyone see any issues with this? Such as indexing or fragmentations that may occur? Or perhaps even a better way of doing it?
Just to confirm, there is no business meaning in this key, the user will never see it (except perhaps in the parameters of a URL) nor use it.
I look forward to your opinions and appreciate your time, Thanks a million :)
This explains the hilo algorithm which you refer to: What's the Hi/Lo algorithm?
It's the often-used solution to "disconnect" problems such as yours. For i.e. if you're using Hibernate/nHibernate, it's one of the recommended primary key options.