How Replication Works with inserts and Autonumber pKs

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.

Thanksmake 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|||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.|||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.|||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?|||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.