Replication and uniqueidentifier

Hope someone can help with this.
I have one database where all the tables were joined by a auto incrementing
column.
Now we are trying to see if we can move to having multiple databases
(replication) across different offices which has the same tables. Each office
would merge in their records but can see other offices records as well and
possibly make updates to it. We would try to implement two way merging.
We are coming to the realization that we can't use the autoincrementing id
and now possibly join all the tables using a unique identifier. We can't use
the auto id since we have multiple sql servers now and conflict could occur.
Two questions:
1. Is joining by unique identifier good
2. Can we implement merge replication using this unique identifier as I read
that sql server adds its own unique identifier column.
Any help would be appreciated.
Abdul Rauf
In general avoid guids when possible. Check out this link for a discussion
of the reasons why.
http://www.aspfaq.com/show.asp?id=2504
Basically to avoid primary key collisions you need to ensure that the data
which is entered in one database will never have the same primary key value
as data which is entered in another database.
What you really need to do is partitioning. You can use different identity
seeds to achieve this please check out
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80
HTH
"Abdul Rauf" <AbdulRauf@.discussions.microsoft.com> wrote in message
news:3F3DAC60-B9DE-446C-BF8C-12CF139A106B@.microsoft.com...
> Hope someone can help with this.
> I have one database where all the tables were joined by a auto
incrementing
> column.
> Now we are trying to see if we can move to having multiple databases
> (replication) across different offices which has the same tables. Each
office
> would merge in their records but can see other offices records as well and
> possibly make updates to it. We would try to implement two way merging.
> We are coming to the realization that we can't use the autoincrementing id
> and now possibly join all the tables using a unique identifier. We can't
use
> the auto id since we have multiple sql servers now and conflict could
occur.
> Two questions:
> 1. Is joining by unique identifier good
> 2. Can we implement merge replication using this unique identifier as I
read
> that sql server adds its own unique identifier column.
> Any help would be appreciated.
> Abdul Rauf

Replication and uniqueidentifier

Abdul,
it's better not to use GUIDs for PKs as they are too
large (4x the size of an integer).
Identity columns can be used in merge replication - you
can enable sql server to maintian the ranges to ensure
they never overlap. Have a look at the article properties
of a table containing identity columns before it is
subscriber to, and there is a checkbox on the identity
range tab. The easiest way is tp ensure that the ranges
are so wide that there will never be any need for them to
be updated.
HTH,
Paul Ibison (SQL Server MVP)[vbcol=seagreen]
Thank you Paul for your reply. I used int primary key to avoid using GUID
because of speed issues but did not foresee that we would use replication
later on. Since there is a limit on using a integer key there is a possiblity
that all the numbers could get taken as there are a lot of rows, and many
rows get inserted daily.
Maybe a string key could work but GUID's are guaranteed to be unique
according to Microsoft. Maybe I can alleviate the speed issue by using a
combination of indexes and selecting only those records which need to be
viewed in the front end. This was the plan but I wanted to make sure that
GUID columns would not be affected when implementing merge replication as I
thought it adds its own guid column.
-Abdul Rauf
"Paul Ibison" wrote:

> Abdul,
> it's better not to use GUIDs for PKs as they are too
> large (4x the size of an integer).
> Identity columns can be used in merge replication - you
> can enable sql server to maintian the ranges to ensure
> they never overlap. Have a look at the article properties
> of a table containing identity columns before it is
> subscriber to, and there is a checkbox on the identity
> range tab. The easiest way is tp ensure that the ranges
> are so wide that there will never be any need for them to
> be updated.
> HTH,
> Paul Ibison (SQL Server MVP)
>
|||Abdul, integers go from -2billion to 2 billion so you're only going to have
a problem if the data spans >4billion!
If that is the case, you could partition the data using a composite key,
with one column as a subscriber identifier.
HTH,
Paul Ibison (SQL Server MVP)[vbcol=seagreen]