PRB: Non-Unique Clustered Index on a Published Table Causes Incorrect Data to Replicate to Subscribers (308843)


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q308843

SYMPTOMS

In a transactional publication, if you add a non-unique index on a table that you have defined as an article, incorrect data may replicate to Subscribers. For example, blank spaces might be included for the variable length columns.

CAUSE

The Replication LogReader Agent uses the column offsets to generate commands that replicate to the Subscriber. If you create a non-unique clustered index on a published table or if you drop a unique clustered index and replace it with a non-unique clustered index, the column offsets for all variable length columns change. The column-offset changes cause the Replication LogReader Agent to generate invalid commands.

WORKAROUND

To work around this problem:
  • Do not add a non-unique clustered index on a published table that belongs to Transactional Publication.

    -or-

  • If you need to add a non-unique clustered index to a published table (after the table replicates to a Subscriber):

    1. Drop the existing subscriptions.
    2. Add the new index.
    3. Re-create the subscriptions.

MORE INFORMATION

By default, SQL Server adds a clustered index on the column that is set as the primary key. However, based on the database design you might choose to create the primary key with a non-clustered index. You may need to create the unique clustered index on another non-primary key column for design reasons.

By default, replication propagates the clustered index of a published table to the subscriber during the initial synchronization process (snapshot process). If you add a clustered unique index to a table after the publication of the table, the Replication LogReader Agent takes into account the column offsets, which then causes the distribution of incorrect commands.

With SQL Server 2000 Service Pack 2, a new error displays if you add a non-unique clustered index to a table that is included in a transactional publication. The error text presented is:
Error 1960, Cannot create a non-unique clustered index on a table after it is published for transactional replication. Drop all publications that include this table before creating the index.
The SQL Server 2000 Service Pack 2 upgrade does not correct existing non-unique clustered indexes that you added prior to applying SQL Server 2000 Service Pack 2. You must correct those indexes by using the suggestions in the Workaround section of this article.

Modification Type:MajorLast Reviewed:10/30/2003
Keywords:kbpending kbprb KB308843
©2003 Microsoft Corporation. All rights reserved.