Microsoft KB Archive/933838

= Additional updates and additional inserts may be performed during synchronization when the merge publication contains join filters in SQL Server 2005 =

Article ID: 933838

Article Last Modified on 11/20/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Workgroup Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Standard X64 Edition
 * Microsoft SQL Server 2005 Enterprise X64 Edition
 * Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
 * Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems

-



SUMMARY
Consider the following scenario. You have a merge publication that is filtered. You define a join filter between two merge articles. A new row is inserted into the parent table of the join filter. In this scenario, you may notice one of the following during synchronization:
 * An insert and an update are performed.
 * Two inserts are performed.

You may expect only one insert to be performed during synchronization.



MORE INFORMATION
The following information describes these two situations. In the first situation, you may notice that an additional update is performed. In the second situation, you may notice that an additional insert is performed.

Situation 1: An additional update is performed after a new row is inserted
If a new row is inserted into the parent table of the join filter, the newly inserted row may qualify new rows in the child table for membership in the subscription. Additional updates may be performed if the following conditions are true in the merge publication:
 * Columns that are referenced in the join filter do not have the primary key and foreign key relationship.
 * The parent/child relationship of the tables is switched in the join filter. For example, the right side of the join filter is the parent table. The left side of the join filter is the child table.

The following example explains the need for the additional update. You have two tables: the Location table and the Patient table. The parent table is the Location table. The child table is the Patient table. For each Location row in the parent table, there are multiple Patient rows in the child table.

The following is the join filter clause. Patient.LocationID=Location.LocationID The parent/child relationship of the tables is switched in the join filter. The Patient table and the Location table in the join filter have a many-to-one relationship instead of a one-to-many relationship.

The rows in the Patient table are as follows.

The rows in the Location table are as follows.

The filtered data partition for the Sub1 subscriber will have the following result on the subscription.

The Patient table

The Location table

You run the following statement to insert a new row into the Patient table. Insert Into Patient Values ('EPatient', 3, 'Sub1') When you synchronize the subscriber, the row is inserted into the Patient table. However, you notice an additional update in the following status message:

Merge completed after processing 2 data change(s) (1 insert(s), 1 update(s), 0 delete(s), 0 conflict(s)).

The additional update is not necessary if the following conditions are true:
 * The columns that are referenced in the join filter have the primary key and foreign key relationship.
 * The parent table is on the left side of the join filter. The child table is on the right side of the join filter.

The merge process has special performance optimizations that are available if the joined column in the parent table is unique. The merge agent must perform additional operations to make sure that the correct rows are sent to each replica if the following conditions are true:
 * The parent/child relationship in the join filter is switched. That is, the parent table is on the right side of the join filter. The child table is on the left side of the join filter.
 * No referential integrity constraint is defined on the join filter columns.

If the join condition is based on a unique column, you can set the join unique key option to True so that the join filter can obtain the special performance optimizations.

Situation 2: An additional insert is performed after a new row is inserted
Situation 2 uses the same example as Situation 1. However, you run the following statement instead. Insert Into Patient Values ('EPatient', 5, 'Sub1') A LocationID of 5 does not exist at the subscriber. Therefore, the new Patient row that has a LocationID of 5 qualifies as the new Location row in the Location table at the subscriber. Additionally, the new Location row must be inserted into the Location table at the subscriber. Therefore, you notice two inserts in the following status message:

Merge completed after processing 2 data change(s) (2 insert(s), 0 update(s), 0 delete(s), 0 conflict(s)).

Keywords: kbinfo kbtshoot kbexpertiseadvanced KB933838

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.