SQL Server – How Uniquifier Works with Replication

sql server

In SQL Server, if I have a non-unique clustered index on a table, how does replication work for that table?

For example:

CREATE TABLE MyData (
    MyDataID uniqueidentifier not null,
    MyData varchar(max) not null,
    RecordDateTime smalldatetime not null,
    PRIMARY KEY NONCLUSTERED (MyDataID ASC)
)

CREATE CLUSTERED INDEX IX_MyData_RecordDateTime ON MyData (
    RecordDateTime ASC
)

(For purposes of the question, the table structure is more illustrative than exact)

I know that SQL Server will add a hidden 4-byte "uniquifier" to rows with duplicate RecordDateTime values, but in this case, how are the rows replicated?

Specifically, if there are two rows R1 and R2 on two publishers P1 and P2, if R1.RecordDateTime is unique on P1 and R2.RecordDateTime is unique on P2, but R1.RecordDateTime is equal to R2.RecordDateTime, how are the rows merged onto a shared subscriber S?

Best Answer

Replication requires a primary key constraint on the replicated tables, not a clustered index. Primary key constraints enforce uniqueness. If you have more than one publisher to the same subscriber, you'll need to ensure that there are no collisions. This is often done with identity columns. For instance, one server would use negative numbers, the other server would use positive numbers.

If you also have a non-unique clustered index, the uniqifier doesn't get transferred as it's not part of the INSERT/UPDATE/DELETE command. The uniquifier value will be added on the subscriber and would be different than the value on the publisher.

Related Question