Hi All
I am wanting some advice on database design within a P2P environment.
It is advise that GUID are used when designing tables within a P2P solution so that all IDs that are generated are unique across the databases. Is this true?
Also, if GUID are used, what is the "Best Practices" for the PK/Clustered index given that it is not advised to use a GUID.
Thanks in advance
JL
GUIDs are certainly a way to ensure that distributed data sources can all merge into a single entity without (or, at least, with a low probability of) collisions.
However, you shouldn't use a GUID for your clustered index, because it will cause big time fragmentation. Oleg put it nicely in a comment on this question:
A clustered index should be as cheap as possible and ever-increasing.
I tend to agree, and would probably implement a solution where the primary key was a GUID, specified with a sufficiently low fill factor and reindexed as often as possible, and the clustered index was based on an IDENTITY.
A lot of people say that using NEWSEQUENTIALID() is good enough for a clustered index, because it always increases - but it doesn't. It always increases as long as you don't reboot the server - which, for me, is just setting yourself up for a problem in the future.
