Kay Mohd (12/12/2007)
What kind of mess i'll be seeing in the future?
Can't GUID size indexing problem be solved with partitioning?
Can you suggest other alternatives to GUID, based on the above scenario?
Thanks in advance
- I'm not sure what you mean by the first question. You might be asking if using GUIDs will make your data more difficult to deal with and the answer is, a little. It's a little easier for a human to look up record number 82342 in two or more different tables than it is to look up record 7823F847-EA50-4D3B-BBE2-6DD2B093C8B8 in two or more different tables. So when you are researching a problem INTEGER ID's make things slightly easier. You might ask yourself how hard is it to join two or more tables though.
- In your situation a GUIDs size poses less of a problem because some of the other options require mutiple keys which causes the alternative indexes to begin to approach the size of the GUID if not exceed it.
- The most obvious alternative is to use a compound key in your situation. Include two columns in your tables, one a server identifier and the other a record identifier. This compound key can be used to uniquely identify each record regardless of which database it's replicated to. Another alternative is to partition your IDENTITY values into ranges and assign each server a range, the problem with that approach is the ranges are finite and eventually you might have to do some maintenance to avoid a key collision. I really despise the partition/range approach as there are some many pitfalls with it. One location might do more business than another yet you might not know that initially and assign the same size range to each location which will cause you to have to do far more maintenance to keep the high volume location up and running than is required to keep the low volume location up and running.
By the way, I would never recommned the IDENTITY partition/range approach in you situation. I've seen it implemented a number of times and it was always ugly and very costly.