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.

how often should indexes be rebuilt?

In oracle you rarely need to rebuild indexes no matter whether you do
massive inserts or deletes. Does the same hold true in sql server?"Ryan" <rgaffuri@.cox.net> wrote in message news:<4OCPb.5817$_H5.4427@.lakeread06>...
> In oracle you rarely need to rebuild indexes no matter whether you do
> massive inserts or deletes. Does the same hold true in sql server?

It shouldn't really be necessary to rebuild indexes, unless there's
some specific issue, such as index corruption, or you're moving tables
and indexes around on filegroups. Sometimes it can be useful to drop
indexes before a large data load, then create them again afterwards.

What is more useful is to UPDATE STATISTICS on tables manually after a
large data operation. Although MSSQL manages statistics automatically,
when you have a very large table it's possible that an update will
affect enough rows to require a new query plan, but not enough to
trigger the automatic statistics update. So it's common to do that
after a large load.

Simon|||Ryan (rgaffuri@.cox.net) writes:
> In oracle you rarely need to rebuild indexes no matter whether you do
> massive inserts or deletes. Does the same hold true in sql server?

I would like to voice a different opinion from Simon. Rebuilding indexes
can have considerable improvement on performance. For instance, some weeks
ago, I was analysed some performance problems at a customer site. One of
the actions I took was running DBCC DBREINDEX on the involved tables.
For the particular test case I had, this reduced execution time with 25%.

While the real solution to the problem was something else - and which I
only found by chance, when I had given up to achieve better than I had -
I think still illustrates that reindexing can have significant effect
on performance.

So if you have a high rate of insertions and deletions into some tables,
reindexing at least weekly can prevent your system from deteroirating.

But there may be exceptions. In our system, there is a table which I've
told our admin not to reindex, because the way the table is updated, the
rate of defragmentation is small, and reindexing usually have the effect
that the nightly load into this table runs for five minutes rather than
30-40 seconds.

You can examine the fragmentation for a table with DBCC SHOWCONTIG.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp