How to: Define and Modify a Join Filter Between Merge Articles (SQL Server Management Studio)
Define, modify, and delete join filters on the Filter Table Rows page of the New Publication Wizard or the Filter Rows page of the Publication Properties - <Publication> dialog box. For more information about using the wizard and accessing the dialog box, see How to: Create a Publication and Define Articles (SQL Server Management Studio) and How to: View and Modify Publication and Article Properties (SQL Server Management Studio).
Note
If you add, modify, or delete a join filter in the Publication Properties - <Publication> dialog box after subscriptions to the publication have been initialized, you must generate a new snapshot and reinitialize all subscriptions after making the change. For more information about requirements for property changes, see Changing Publication and Article Properties.
Join filters can be created manually for a set of tables, or replication can generate the filters automatically based on the relationships between foreign keys and primary keys defined on the tables. For more information on generating a set of join filters automatically, see How to: Automatically Generate a Set of Join Filters Between Merge Articles (SQL Server Management Studio).
To define a join filter
On the Filter Table Rows page of the New Publication Wizard or the Filter Rows page of the Publication Properties - <Publication>, select an existing row filter or join filter in the Filtered Tables pane.
A join filter extends a row filter; therefore you must define a row filter before you can extend the filter with a join. After one join filter is defined, you can extend this join filter with another join filter.
Click Add, and then click Add Join to Extend the Selected Filter.
Create the join statement: select either Use the builder to create the statement or Write the join the statement manually.
If you select to use the builder, use the columns in the grid (Conjunction, Filtered table column, Operator, and Joined table column) to build a join statement.
Each column in the grid contains a drop-down combo box, allowing you to select two columns and an operator (=, <>, <=, <, >=, >, and like). The results are displayed in the Preview text area. If the join involves more than one pair of columns, select a conjunction (AND or OR) from the Conjunction column, and then enter two more columns and an operator.If you select to write the statement manually, write the join statement in the Join statement text area. Use the Filtered table columns list box and the Joined table columns list box to drag and drop columns to the Join statement text area.
The complete join statement would appear like:
SELECT <published_columns> FROM [Sales].[SalesOrderHeader] INNER JOIN [Sales].[SalesOrderDetail] ON [SalesOrderHeader].[SalesOrderID] = [SalesOrderDetail].[SalesOrderID]The JOIN clause should use two-part naming; three-part naming and four-part naming are not supported.
Specify join options:
If the column on which you join in the filtered table (the parent table) is unique, select Unique key.
Warning
Selecting this option indicates that the relationship between the child and parent tables in a join filter is one to one or one to many. Only select this option if you have a constraint on the joining column in the child table that guarantees uniqueness. If the option is set incorrectly, non-convergence of data can occur.
By default, merge replication processes changes on a row-by-row basis during synchronization. To have related changes in rows of both the filtered table and the joined table processed as a unit, select Logical record (Microsoft SQL Server 2005 and later versions only). This option is available only if the article and publication requirements for using logical records are met. For more information see the section "Considerations for Using Logical Records" in Grouping Changes to Related Rows with Logical Records.
Click OK.
If you are in the Publication Properties - <Publication> dialog box, click OK to save and close the dialog box.
To modify a join filter
On the Filter Table Rows page of the New Publication Wizard or the Filter Rows page of the Publication Properties - <Publication>, select a filter in the Filtered Tables pane, and then click Edit.
In the Edit Join dialog box, modify the filter.
Click OK.
To delete a join filter
- On the Filter Table Rows page of the New Publication Wizard or the Filter Rows page of the Publication Properties - <Publication>, select a filter in the Filtered Tables pane, and then click Delete. If the join filter you delete is itself extended by other joins, those joins will also be deleted.
See Also
Tasks
How to: Define a Logical Record Relationship Between Merge Table Articles (SQL Server Management Studio)
How to: Define and Modify a Parameterized Row Filter for a Merge Article (SQL Server Management Studio)
How to: Define and Modify a Static Row Filter (SQL Server Management Studio)
How to: Reinitialize a Subscription (SQL Server Management Studio)
Concepts
Join Filters
Parameterized Row Filters