Menu
I'm in the middle of designing a new database that will need to support replication and I'm stuck on the decision what to choose as my primary key.
In our current database for primary key we use two int columns, first column is identity and the other is used to describe on which server the row is inserted. Now I want to avoid using two columns for primary key, and just use one column instead. So far I have two way of doing this:
Use GUID for my primary key
This one will ensure that there is always a unique key across any number of servers. What I don't like with this one, is that the GUID is 16bytes in size, and when used for foreign key across many tables it will waste space. Also it is harder to use it when writing queries, and it will slower to query.
Use the int or bigint, and manually specify the seed and increment value for every table on each server. For example if there is two servers, the X table on the first server will start from number 1, and on second server it will start from number 2, each will increment by 2. So there would be (1,3,5,...) on first, and (2,4,6,...) on second server. The good thing with this design is that it is easier to use when writing queries, it's fast, and it use less space for foreign keys. Bad thing is that we never know how many servers will be running so it's harder to tell what will be the increment value. Also it's harder to manage the schema change on server.
What is the best practice for managing multiple servers, and what's the best way, if any, to do in this kind if situations?
200
An identity is simply an auto-increasing column. A primary key is the unique column or columns that define the row. These two are often used together, but there's no requirement that this be so.
In many cases an identity column is used as a primary key; however, this is not always the case. It is a common misconception that an identity column will enforce uniqueness; however, this is not the case.
Each time a row is inserted into the table, the identity column is assigned the next highest value. If the identity reaches the maximum value, inserts will fail.
Your question is a good one, and one that is asked often.
From a maintenance perspective, I would absolutely go with GUIDS. They are there for a reason. Somewhere along the line you might run into complex operations moving and re-replicating your data, and then the other options can make it a little more complex than it needs to be.
There is a very nice short read about the various options here:
http://msdn.microsoft.com/en-us/library/bb726011.aspx
As for the Replication part - if done properly, there are no real headaches with replication.
72
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
