Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Unanswered: How Replication Works with inserts and Autonumber pK's

    Hi,
    I am using SQL Server replication.
    I have published articles and my subscribers are some users with pocket pc.

    I have a couple of tables with Identity primary keys.
    I have already set them to "Not for replication".

    My questions:
    For tables with identity values, say tblContact, when different users add new contacts new numbers are generated.
    What happens when they sync? if two users added a new records , and both have the same number, will sql server consider one as updating the other? Or it will add both with new numbers to the master table and update both subscriptions.


    In other words, how should the autonumber primary keys be handled in replication? should I create a composite key, say userID+autonumber to avoid such a situation?

    I hope my question is clear.

    Thanks
    The intellect is better than desire, for the intellect makes you a king over your destiny, and desire makes you a slave of your destiny.

  2. #2
    Join Date
    Jun 2003
    Posts
    3

    How replication works with inserts

    make your identity keys guids (sql type uniqueidentifier, set default property to Newid() ) or random integers. choice depends on number of records your table will have. ie: < 1billion, could probably get away with random integer with low risk of collision, > 1 billion better use guids. I personally use guids for most stuff because I don't have to ever worry about collisions and storage is not an issue, but this may be an issue for you on a pocketpc.

    hth
    burt

  3. #3
    Join Date
    Jun 2003
    Posts
    3

    How replication works with inserts

    btw...

    if you need the new identity right away and your provider doesn't support refreshing the underlying value you may just wish to generate your own guid (don't set a default on the field). That's one other advantage to this method.

    another way: if you're using stored procedures you could add an output parameter to return the newly created guid. this is especially helpful if your programming language doesn't have built in facilities to create a guid.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I like the newid() idea, but if you are set on identity values, you can make them mutually exclusive sets. This will require you, however, to declare how many servers participate in the replication at the time of setup, because there won't be much going back (although I have not read up on dbcc fixidentity (sp?) lately).

    Server 1 has

    col1 int identity (1, 3) primary key

    Server 2 has

    col1 int identity (2, 3) primary key

    Server 3 has

    col1 int identity (3, 3) primary key

    This way, server 1 has values 1,4,7,10,13,...
    server 2 has 2,5,8,11,...
    and server three has 3,6,9,12,...
    Server 4 gets no break.

  5. #5
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    Thanks for your reply,
    I read up the Identity range and GUID in books online.

    But I have a question:
    Instead of using guid and identity range, what if we use a composite key, for example use an identity column combined with the userID?

    How is that?
    The intellect is better than desire, for the intellect makes you a king over your destiny, and desire makes you a slave of your destiny.

  6. #6
    Join Date
    Jun 2003
    Posts
    3

    How Replication Works with inserts and Autonumber pK's

    then you'd might as well just find the natural key, assuming one exists?
    compound primary keys, while fully supported, will make your queries much more complex.

    the project i'm working on now has several compound primary keys (one with 4 fields in the key) and the queries are a nightmare is some places - especially because this is a big project with well over 100 stored procs.

    note also that i'm not sure you can do a "compound foreign key". in other words you may still have to have an artificial primary key to establish relationships.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •