2

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?

2

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.

  • Right, but the primary key is unique. My concern is that the clustered index must also be unique (and SQL Server will force it to be unique if not declared as unique). – David Kleszyk Dec 8 '16 at 15:48
  • (Sorry, didn't mean to hit enter) In an even more simplified example, if the table is (C1 (PK), C2 (NON-UNIQUE CLUSTERED), (UNIQUIFIER(hidden from user)), on P1 the row is (1, 1, NULL) on P2 the row is (2, 1, NULL). When these are merged onto the subscriber the rows will be (1, 1, ???), (2, 1, ???). The uniqueness of the PK is preserved, but what will happen to the uniquifier value? – David Kleszyk Dec 8 '16 at 15:52
  • But replication replicates the INSERTs, UPDATEs, and DELETEs. The uniquifier value isn't present in those commands, so that value is being added once the command runs on the subscriber. – Tara Kizer Dec 8 '16 at 15:57
  • I edited my answer to include that info. I have not tested it, but that is my understanding based on what get replicated (the commands). – Tara Kizer Dec 8 '16 at 16:05
  • Okay, thanks. I don't currently have multiple servers to test the replication, but I know that it's a future goal so I wanted to design the tables with that in mind. – David Kleszyk Dec 8 '16 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.